Hi Guys,

I'm having a performance/speed issue when trying to apply a set of filters to a pivot table using VBA.

In my excel doc - I have a tab called "FilterTable" which I hold a list of pivot field names and pivot field items - in columns A and B of the tab - starting on the first row - as shown below:

---------------------FilterTable---------------------
Gender Male
City London
Department Finance


This tab represents the set of filters that I wish to apply to my pivot table - so for example in this case to filter on Gender = "Male" and City = "London" and Department = "Finance"


To actually apply the filters - I have written the following VBA:

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim myFilterField as String
Dim myFilterItem as String

Set pt = Sheets("pivot").PivotTables("PivotTable1")
x = 1
    
pt.ClearAllFilters

Do While Sheets("FilterTable").Cells(x, 1).value <> ""
    
     myFilterField = Sheets("FilterTable").Cells(x, 1).value
     myFilterItem = Sheets("FilterTable").Cells(x, 2).value

     Set pf = pt.PivotFields(myFilterField)
                    
     For Each pi In pf.PivotItems
          If pi.name <> myFilterItem then pi.visible = false
     Next pi

    x = x + 1
    
Loop

The code works fine - but it is just incredibly inefficient - as say for example I have 100 departments - I would need to loop through all of these and set all the field items that I do not want to be visible to false (takes about 0.5 sec for each item). And obviously the more filters I apply - the worse the performance will become......I don't even want to think about how this would work when it comes to filtering on dates!!!!

I am aware that this vba will not work for multiple value filtering on the same field - but i'm just giving an example to highlight the issue I am having.

I have done the following which has not improved performance to the level I require:
  • application.calculation =xlmanual
  • application.enableevents = false
  • application.screenupdating = false
  • pivottable.manualupdate = false

----

I'm not that experienced with pivioting/vba - so I think there must be a more efficient way to filter in VBA using pivot tables that doesn't involve iterating around all items in a field and setting them to false. Maybe one of you lovely people can help me out !!!

Many Thanks!