Geek Answers Handbook

Speeding up VBA code pivot table filtering

I have a pivot table with a rotary field and contains many elements. I have VBA code logic to decide if the rotation value should be visible or not. The problem is that excel recounts the pivot table for each displayed or hidden field, which makes it very slow. I would like something where it is recounted only once, after all the values ​​have been set. I tried using Application.Calculation = xlCalculationManual, but it did not help.

The vba code I use is similar to this

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 have to do this manually by unchecking the "show all" box and double-checking the fields that I want to see. This will force Excel to recount once and display only the rotation elements that I want to see. I would like to do the same with VBA code.

I even tried to use

Application.ScreenUpdating = False
Application.DisplayAlerts = False

but does not work.

+5
source share
5 answers

ABOUT! I just decided that one:

At the beginning of the code, disable auto-update as follows:

PivotTable.ManualUpdate = True

And then at the end of the code, return it:

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

I found this thread that is looking for help writing code that determines whether the value of the pivot table should be displayed. What is behind your oPivotField? This is the part that I am missing!

+7
source

PivotTable ManualUpdate, , .

http://www.ozgrid.com/VBA/hide-pivot-fields.htm

+4

. .

:

Application.Calculation = xlCalculationManual

:

Application.Calculation = xlCalculationAutomatic
0

pivottable.ManualUpdate [= ]
True RefreshTable, ,
False RefreshTable .
- False.
reset False ()

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()

, ManualUpdate ( , reset false, true, )

, Excel, :
Pivot Refresh vs. Update - ?

:
: Excel VBA .NET
: ,
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

0

"excel 93-2007". . , .

-1

More articles:


All Articles