Speed up pivot table filtering VBA code

Question

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 code that I am using is something like 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 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.

I even tried using

Application.ScreenUpdating = False
Application.DisplayAlerts = False

but didn't work.

Answer 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!

Answer 2

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

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