Pivot item filtering performance with VBA

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:

Please Login or Register  to view this content.

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!

Thanks xlnitwit. I meant to write pivottable.manualupdate = True

So I have changed my code to the following based on your advice:

Please Login or Register  to view this content.

----

However - whilst this does apply a filter to the pivot table (i can see it applied on the actual table in the pivot table details view) - it doesn't actually filter the results at all.

I tried to do this action manually on the pivot table - as per the picture below - and it also had no action on the pivot table data.
manual.png

I'm also not sure whether the approach you suggest will work when it comes to applying multiple filters to one field - e.g. filtering on department = "Finance" or "Sales"

Hi xlnitwit - thats right I don't have the field in the table - and it cannot go in the table as I need my table in a certain format for a graph.

I do have all of the fields as slicers though - however I don't believe there is a method like:

pf.PivotFilters.Add2 Type:=xlCaptionEquals, Value1:=myFilterItem

for slicers???

but i'm guessing looping through the items of the slicer is going to run in to exactly the same performance issues as those I experienced with looping through pivottable items.....