Speeding up VBA Code that Sets Pivot Table Filters

Question

I've written out some VBA code that sets various filters onto pivottables. The problem I am experiencing is that it is extremely slow and bogs down my system heavily.

I've tried implementing various things to speed up the code, but nothing really has that much of an effect.

The reason I am doing it this way is the filters need to be dynamic and I am using multiple data sources, so just using a single slicer doesn't work in my case.

Here is my code:

 Private Sub Worksheet_Calculate()
 Application.ScreenUpdating = False
 Application.EnableEvents = False
 Application.Calculation = xlCalculationManual
 Application.DisplayStatusBar = False
 ActiveSheet.DisplayPageBreaks = False
 Dim DivRef, RegRef, DistRef, ZoneRef As String
 DivRef = Sheet5.Range("AH6").Value
 RegRef = Sheet5.Range("AH7").Value
 DistRef = Sheet5.Range("AH8").Value
 ZoneRef = Sheet5.Range("AN4").Value
With Sheet5.PivotTables("PivotTable21")
 .ManualUpdate = True
 .PivotFields("Division2").CurrentPage = DivRef
 .PivotFields("Region2").CurrentPage = RegRef
 .PivotFields("District2").CurrentPage = DistRef
 .ManualUpdate = False
End With
With Sheet5.PivotTables("PivotTable9")
 .ManualUpdate = True
 .PivotFields("Division2").CurrentPage = DivRef
 .PivotFields("Region2").CurrentPage = RegRef
 .PivotFields("District2").CurrentPage = DistRef
 .ManualUpdate = False
 End With
 Application.EnableEvents = True
 Application.ScreenUpdating = True
 Application.Calculation = xlCalculationAutomatic
 End Sub

In reality, there are a number of pivottables that are being filtered, but for the sake of this post, I am only posting PivotTable21 and PivotTable9's filters, though it is all more or less the same code.

I've been told that part of the problem might be that I am using calculated fields in my pivottables, and that may bog down some of the code as well.

Does anyone have any tips for speeding this code up?

• IvenBach: You need to dim each variable. Dim DivRef, RegRef, DistRef, ZoneRef As String only ZoneRef is String the rest are Variant`.
• ranopano: I may have done this wrong, so let me know... DivRef, RegRef, etc will refer to a String, like "West" or "East", etc. I'm guessing I can remove the .value at the end of the reference, though I'm not sure if this will improve speed at all. Should I still set them to Variant?

Answer 1

I find that when you set PivotTable.ManualUpdate = True, Excel seems to often set that back to False for many of the changes you can make to PivotTables. It does depend on the change, but I think changing PivotFields is one of those things that triggers this.

So you could try going to every PivotField change and resetting ManualUpdate to True after it. Such as this:

With Sheet5.PivotTables("PivotTable21")
 .ManualUpdate = True
 .PivotFields("Division2").CurrentPage = DivRef
 .ManualUpdate = True
 .PivotFields("Region2").CurrentPage = RegRef
 .ManualUpdate = True
 .PivotFields("District2").CurrentPage = DistRef
 .ManualUpdate = False
End With

I know it seems silly, but I've seen this work.

• ranopano: Hmmm interesting. I wouldn't have thought of this. I will give it a try and see if it speeds things up a bit. Thanks!
• PerryJ: You could test this theory by doing Debug.Print to the Immediate windows and seeing if the ManualUpdate for the PivotTable in question is still set to True. Prior to updating it everywhere.

Answer 2

I think a problem you might have is that you have this in Worksheet_Calculate and you set your Application.Calculation to automatic -

Private Sub Worksheet_Calculate()
...
Application.Calculation = xlCalculationAutomatic
End Sub

You are applying all of these filters every time anything calculates on whatever sheet this is on. Do you need that? I doubt it - maybe set your sheet to manual calculation or move the event out of the calculate event and set up a button or something.

---

When you declare variables, you need to give them all a type -

Dim DivRef, RegRef, DistRef, ZoneRef As String

This declares DivRef, RegRef and DistRef as Variant and only ZoneRef as string. Variants can slow down your code, but I doubt that's really the problem here. It's good practice, however, to always type them. You need to type each one e.g.

Dim divRef as String, regRef as String, distRef as String, zoneRef as String

Or more appropriately-

Dim division As String
Dim region As String
Dim district As String

I don't see you using zone so I didn't include it.

I also changed the naming, you want your names to be clear and concise.

• ranopano: Really appreciate the comment! To answer your question, yes, I am applying these filters every time something calculates on the sheet. I'm using excel 2010 without PowerPivot. The issue is that I have 5-6 different data sources and I need all the data sources to be controlled by 1 slicer. Anytime a slicer item is clicked, RegRef, DistRef, etc, will change based on the slicer selection and the VBA filters will filter the pivot table, even if it's not connected to the original slicer.