Poor performance updating Excel slicer selection using VBA

Question

I am simulating a click on an Excel Slicer using VBA but have run into serious performance problems. The user clicks on a column graph with dates on the X-axis. When clicking on a column, the corresponding date is selected in a slicer containing the list of dates. The list will continue to grow with time. The only way (to my knowledge) to set slicer selection for non-OLAP data sources (my case) is to set selected = true individually for each slicer item. As a recalculation is triggered on each setting this is very slow for slicers with many items. Small code example showing the problem: ``` On Error GoTo Err_Handler: Dim SC As SlicerCache Set SC = ActiveWorkbook.SlicerCaches("Slicer_DATE") Dim SI As SlicerItem Application.EnableEvents = False Application.Calculation = xlCalculationManual For Each SI In SC.SlicerItems SI.Selected = True Next Err_Handler: Application.Calculation = xlCalculationAutomatic Application.EnableEvents = True ``` Similar questions have been asked before: Select…

Accepted Answer

PivotTables have a `ManualUpdate` property which can be set to `True`. Setting this property for all your pivots might help speed up your code.

Please try adding this right above where your update slicer code is:

```
Dim PT As PivotTable
Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook

For Each ws In wb.Sheets
    For Each PT In ws.PivotTables
        PT.ManualUpdate = True
    Next PT
Next ws
```

And then add this after you've updated the slicer:

```
For Each ws In wb.Sheets
    For Each PT In ws.PivotTables
        PT.ManualUpdate = False
    Next PT
Next ws
```

For more information: Speed up pivot table filtering VBA code Turn Off PT Calc MSDN: ManulaUpdate

Hope that helps!

Poor performance updating Excel slicer selection using VBA

Solution

Problem

Related problems