CODEWITHSUNDEEP
excelvba
Buras
Buras

Reputation: 3099

How to select (All) in a pivot table filter

I need (All) to be selected in my pivot table

I tried the following

Dim pf As PivotField
 Set pf = Worksheets("xxx").PivotTables("PivotTable1").PivotFields("myFilterField")
   For Each Pi In pf.PivotItems
          Pi.Visible = True
        Next Pi

This works very slowly, and not well

Another option does not work at all . It selects only some of the items that were selected previously

Worksheets("xxx").PivotTables("PivotTable1").PivotFields("myFilterField").CurrentPage = "(All)"

Upvotes: 0

Views: 24282

Answers (2)

Pinang
Pinang

Reputation: 11

Try the below code:

Worksheets("xxx").PivotTables("PivotTable1").PivotFields("myFilterField").ClearAllFilters

Upvotes: 1

Cor_Blimey
Cor_Blimey

Reputation: 3310

You could be calling .Visible = True for a couple of reasons so hopefully one of the below covers what you are trying to do:

If you mean you want to set all the Pi to be visible in one step then rather than enumerating through the Pi in pf.PivotItems just:

pf.ShowAllItems = True

If, on the other hand, you mean you want clear all filters then:

pf.ClearAllFilters()

Or, if you want to just clear manual filters (i.e. not filters applied against an actual row/column filed but rather against a filter field) then:

pf.ClearManualFilters

Consult help on PivotField Object Members for the full list

Upvotes: 4

Related Questions