Search code examples
excelpivot-tablevba

Remove all from Pivot filter in VBA

I am trying to remove every country in the "countryName" filed list within a pivot table.

however I get a runtime error 1004, which I believe is down to trying to eliminate the "All" selection. My code is below:

With ActiveSheet.PivotTables("PivotTable1").PivotFields("countryName")
.ClearAllFilters
    .EnableMultiplePageItems = True
    .PivotItems("All").Visible = False
    .PivotItems("GREAT-BRITAIN").Visible = False
End With

Where "countyName" is the field GREAT-BRITAIN is the only country I want displayed

I could do this manually but every time I import new data the country list could differ hence I want to remove "all" then just leaving "GREAT-BRITAIN"

Many thanks to anyone who can help

Solution

  • First up, a couple of alternatives to VBA, in case you haven't considered them. You can use a Slicer to do this with the exact same outcome. Or if you want the user to choose a country from a dropdown, you can use the approach I outline at http://dailydoseofexcel.com/archives/2014/08/16/sync-pivots-from-dropdown/

    I take it from the line .EnableMultiplePageItems = True in your code that you are dealing with a PageField? If so, you want to keep .EnableMultiplePageItems set to false, and just change the PageField property. 

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("countryName")
    .ClearAllFilters
    .CurrentPage = "GREAT-BRITAIN"
End With

    Otherwise you will have to iterate through every item (other than GREAT-BRITAIN) in your PivotTable and set the .visible status to false, which is very slow. (See my post at the following link, as it discusses how to overcome bottlenecks when filtering Pivots with VBA: http://dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/)

    If your field is NOT a PageField, then you can still do this quickly by setting up a 'Master' PivotTable somewhere out of sight, putting the field of interest in the master as a PageField, connecting the Master PivotTable to PivotTable1 ('Slave') with a Slicer, and using the code snippet above on the hidden Master to set the PageField instantly on one item. The Slicer then syncs the Master with the Slave instantly. I use this approach at VBA to connect slicers (looking for improvements to code)