加快数据透视表过滤VBA代码

I have a pivot table with a pivot field and contain many items. I've VBA code logic to decide if the pivot value should be visible or not. The problem is excel recalculates pivot table for each field shown or hidden which makes it very slow. I would like something where it recalculates only once, after all the values are set. I tried using Application.Calculation = xlCalculationManual but it didnt help.

我有一个带有枢轴字段的数据透视表，包含许多项目。我有VBA代码逻辑来决定是否应该显示透视值。问题是excel重新计算显示或隐藏的每个字段的数据透视表，这使得它非常慢。在设置了所有值之后，我想要只重新计算一次的东西。我尝试使用Application.Calculation = xlCalculationManual，但它没有帮助。

vba code that I am using is something like this

我正在使用的vba代码是这样的

For i = 1 To oPivotField.PivotItems.Count
    If (oPivotField.PivotItems(i).Name = "TestCondition") Then
        oPivotField.PivotItems(i).Visible = True   'Recalulates pivot table
    Else
        oPivotField.PivotItems(i).Visible = False 'Recalulates pivot table
    End If
Next

I am to do this manually by uncheck the "show all" box and re-check for the fields I want visible. This cause Excel to recalculate once and show only the pivot items I want visible. I would like to do same thing via VBA code.

我要手动取消选中“全部显示”框并重新检查我想要的字段。这会导致Excel重新计算一次并仅显示我想要的透视项目。我想通过VBA代码做同样的事情。

I even tried using

我甚至尝试过使用

Application.ScreenUpdating = False
Application.DisplayAlerts = False

but didn't work.

但没有奏效。

5 个解决方案

#1

Oh! I just solved that one:

哦!我刚刚解决了那个：

At the beginning of your code, turn off the auto-update like this:

在代码的开头，关闭自动更新，如下所示：

PivotTable.ManualUpdate = True

And then at the end of the code, turn it back on:

然后在代码的最后，将其重新打开：

ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

I found this thread searching for help writing code that determines if a PivotTable value should be visible. What is behind your oPivotField? That's the part I'm missing!

我发现这个线程正在寻找帮助编写代码的帮助，该代码确定数据透视表值是否应该可见。你的oPivotField背后是什么？那是我错过的部分！

#2

PivotTable objects have a ManualUpdate property which might be what you are looking for.

数据透视表对象具有ManualUpdate属性，可能正是您要查找的属性。

See http://www.ozgrid.com/VBA/hide-pivot-fields.htm for some related code

有关相关代码，请参见http://www.ozgrid.com/VBA/hide-pivot-fields.htm

#3

Try to add the following. Helped to give extra speed in my case aswell.

尝试添加以下内容。在我的情况下帮助提供额外的速度。

At the beginning:

一开始：

Application.Calculation = xlCalculationManual

At the end:

最后：

Application.Calculation = xlCalculationAutomatic

#4

pivottable.ManualUpdate [ = setting ]
True causes RefreshTable to clear data from the pivot table, rather than refreshing it
False allows RefreshTable to work normally.
Default is False.
This property is reset to False automatically after the calling procedure ends (important)

pivottable.ManualUpdate [= setting] True导致RefreshTable清除数据透视表中的数据，而不是刷新它False允许RefreshTable正常工作。默认值为False。调用过程结束后，此属性将自动重置为False（重要）

This property should be set to true just before you make an update (e.g. changing pivot item Visible property)
So your code would look like:

在您进行更新之前，此属性应设置为true（例如，更改数据透视表项Visible属性）所以您的代码应如下所示：

For i = 1 To oPivotField.PivotItems.Count
    If (oPivotField.PivotItems(i).Name = "TestCondition") Then
        oPivotField.Parent.ManualUpdate = True
        oPivotField.PivotItems(i).Visible = True  'doesn't recalculate pivot table because ManualUpdate is set to True
    Else
        oPivotField.Parent.ManualUpdate = True
        oPivotField.PivotItems(i).Visible = False 'doesn't recalculate pivot table because ManualUpdate is set to True
    End If
Next

'setting pivot table ManualUpdate property to False might be redundant at this point because it gets reset to false immediately after you set Visible property of oPivotField
oPivotField.Parent.ManualUpdate = False
oPivotField.Parent.Update()

As a conclusion, ManualUpdate property change doesn't stay for long (in my tests, I could see that it gets reset to false as soon as possible, so that's why I recommended you to set it to true whenever you want to make a change for a pivot item)

总之，ManualUpdate属性更改不会持续很长时间（在我的测试中，我可以看到它会尽快重置为false，因此我建议您在需要进行更改时将其设置为true对于枢轴项目）

For more info on what means an update in Excel, you can check the following:
Pivot Refresh vs. Update – is there a real difference?

有关Excel更新的更多信息，您可以检查以下内容：数据透视刷新与更新 - 是否有真正的区别？

References:
Title: Programming Excel with VBA and .NET
By: Jeff Webb, Steve Saunders
Print ISBN: 978-0-596-00766-9 | ISBN 10: 0-596-00766-3
Ebook ISBN: 978-0-596-15951-1 | ISBN 10: 0-596-15951-X

参考文献：标题：使用VBA和.NET编写Excel作者：Jeff Webb，Steve Saunders打印ISBN：978-0-596-00766-9 | ISBN 10：0-596-00766-3电子书ISBN：978-0-596-15951-1 | ISBN 10：0-596-15951-X

#5

-1

Save a copy of your workbook and save it as "excel 93-2007" file. Then try your code. Hope this helps you.

保存工作簿的副本并将其另存为“excel 93-2007”文件。然后尝试你的代码。希望这对你有所帮助。

#1