Deselect all items in a pivot table using vba

This is probably the closest you can get to what you want:

Dim i As Long
.PivotItems(1).Visible = True
For i = 2 To .PivotItems.Count
    .PivotItems(i).Visible = False
Next

This will make the very first option the only selected option (assuming this is within a with that points to the pivotfield). If you know what you want before hand... modify accordingly.


I've found that looping through each data item takes a lot of time, what you can do if you want to filter on a single item in a pivot without looping through all items is use the following code:

ActiveSheet.PivotTables("Your Pivot Name").PivotFields("Your Field Name").ClearAllFilters
ActiveSheet.PivotTables("Your Pivot Name").PivotFields("Your Field Name").PivotFilters.Add _
    Type:=xlCaptionEquals, Value1:="Your string here" 

this is basically a label filter but it worked for me.

Tags:

Excel

Vba