Select/Deselect all Pivot Items

Question

I have a pivot table, and I am trying to select certain pivot items based on values in an array. I need this process to go faster, so I have tried using Application.Calculation = xlCalculationManual and PivotTables.ManualUpdate = True, but neither seem to be working; the pivot table still recalculates each time I change a pivot item.

Is there something I can do differently to prevent Excel from recalculating each time? Or is there a way to deselect all items at once (not individually) to make the process go quicker?

Here is my code:

Application.Calculation = xlCalculationManual

'code to fill array with list of companies goes here    

Dim PT As Excel.PivotTable
Set PT = Sheets("LE Pivot Table").PivotTables("PivotTable1")

Sheets("LE Pivot Table").PivotTables("PivotTable1").ManualUpdate = True
Dim pivItem As PivotItem

'compare pivot items to array.  
'If pivot item matches an element of the array, make it visible=true, 
'otherwise, make it visible=false
For Each pivItem In PT.PivotFields("company").PivotItems
    pivItem.Visible = False 'initially make item unchecked
    For Each company In ArrayOfCompanies()
        If pivItem.Value = company Then
            pivItem.Visible = True
        End If
    Next company
Next pivItem

Answer 1

It seems unavoidable to have the pivotable refreshed every time a pivotitem is updated. However I tried approaching the problem from the opposite angle. i.e.:

1.Validating the “PivotItems to be hidden” before updating the pivottable.

2.Also making make all items visible at once instead of “initially make item unchecked” one by one.

3.Then hiding all the items not selected by the user (PivotItems to be hidden)

I ran a test with 6 companies selected out of a total of 11 and the pivottable was updated 7 times

Ran also your original code with the same situation and the pivottable was updated 16 times Find below the code

Sub Ptb_ShowPivotItems(aPtbItmSelection As Variant)

Dim oPtb As PivotTable
Dim oPtbItm As PivotItem
Dim aPtbItms() As PivotItem
Dim vPtbItm As Variant
Dim bPtbItm As Boolean
Dim bCnt As Byte

    Set oPtb = ActiveSheet.PivotTables(1)

    bCnt = 0
    With oPtb.PivotFields("Company")

        ReDim Preserve aPtbItms(.PivotItems.Count)
        For Each oPtbItm In .PivotItems

            bPtbItm = False
            For Each vPtbItm In aPtbItmSelection
                If oPtbItm.Name = vPtbItm Then
                    bPtbItm = True
                    Exit For
            End If: Next

            If Not (bPtbItm) Then
                bCnt = 1 + bCnt
                Set aPtbItms(bCnt) = oPtbItm
            End If

        Next
        ReDim Preserve aPtbItms(bCnt)

        .ClearAllFilters
        For Each vPtbItm In aPtbItms
            vPtbItm.Visible = False
        Next

    End With

End Sub