Deselect all items in a pivot table using vba

Question

Can some quicly explain the way to deselect all items in a newly created pivot table so that I can go back and select only one or two items? I tried the following:

.PivotItems("(Select All)").Visible = False

Thanks.

Answer 1

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.

Answer 2

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.