Speed Up Slicer Looping in VBA

Question

I am trying to sort a pivot table based on some True/False values on another tab. I've read the easiest way to do this is with a slicer. The code executes successfully, but takes around 45 seconds to run sorting through 230 SlicerItems. Any thoughts on how to speed it up?

Here is my code:

Sub CategoryMacro()
'Runs through Pivot Slicer and selects items from pivot table that meet certain certain TRUE/FALSE on MacroHelper

Dim wb As Workbook
Dim ws1, ws2 As Worksheet
Dim kpicat As String

'Speed Up
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Set wb = ThisWorkbook
Set ws1 = wb.Sheets("MacroHelper")
Set ws2 = wb.Sheets("Visual")

'Prep with some clean-up
ws2.Activate
ActiveWorkbook.SlicerCaches("Slicer_PRODNAME").ClearManualFilter

'Toggles off products with decreasing margin
For i = 2 To 230
    Let kpicat = ws1.Range("A" & i).Value
    If ws1.Range("D" & i).Value = 0 Then ActiveWorkbook.SlicerCaches("Slicer_PRODNAME").SlicerItems(kpicat).Selected = False
Next i

'Un-Speed Up
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

End Sub

I've used a variation on this ReDim code (from here from Chris' response) very successfully on huge datasets, but I'm not sure if it can be applied here. If it can, I'm not sure how I'd apply it.

Sub GetRows()
    Dim valMatch As String
    Dim rData As Range
    Dim a() As Long, z As Variant
    Dim x As Long, i As Long
    Dim sCompare As String

    Set rData = Range("A1:A50000")
    z = rData
    ReDim a(1 To UBound(z, 1))
    x = 1
    sCompare = "aa"
    For i = 1 To UBound(z)
        If z(i, 1) = sCompare Then a(x) = i: x = x + 1
    Next
    ReDim Preserve a(1 To x - 1)    
End Sub

Since your code is functional but just running slowly, it's probably better suit for [Code Review](https://codereview.stackexchange.com/) — dwirony, Apr 17 '18 at 20:06
There's a number of things you can do to radically speed things up, and I'll post a response shortly. But first, what version of Excel are you running? — jeffreyweir, Apr 17 '18 at 21:33
@MattCottrill Cool. Check out my amended answer below and associated links and you'll find what you need to make this lightning fast. — jeffreyweir, Apr 18 '18 at 21:49

jeffreyweir · Answer 1 · 2018-04-18T21:50:05.413

If you ever iterate over PivotItems, set the PivotTable's .ManualUpdate to TRUE while you make your changes, and set it back to FALSE afterwards to avoid the PivotTable refreshing after each and every change. This will radically speed your code up.

See my answer at How to update slicer cache with VBA for code that quickly filters a Slicer on an array.

Note that it's faster still to bring some kind of "True/False" field into your PivotTable by adding a lookup column to your data, and then bringing that field into your PivotTable as a PageField, and setting the PageField value to "TRUE". That will filter your PivotTable without iteration almost immediately.

For more on efficiently programming PivotTables, check out my blog post at http://dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/

Given you've got Excel 2016 you could also use Measures and the DataModel to do this via a linked table, regardless of whether you've got a premium version that has PowerPivot installed. But it would require you to refresh the PivotTable any time your input table changed.

Speed Up Slicer Looping in VBA

1 Answers1