Clearing Power Pivot Slicers

Question

does anyone know a good way of clearing the slicers in a workbook. I have a sheet with many slicers and I would like to have a single button to clear them.

I wrote a bit of VBA to do it but it’s a little slow :

Sub ClearSlicers()
  Dim cache As SlicerCache

  For Each cache In ActiveWorkbook.SlicerCaches
           cache.ClearManualFilter
  Next cache
End Sub

Just wondered if you have anything a little slicker ?

Thanks John

score 0 · Answer 1 · answered Mar 22 '16 at 14:13

0

This will clear all slicers in a sheet

Sub test()

Dim pt As PivotTable
Dim cache As Slicer

For Each pt In ActiveSheet.PivotTables
    For Each cache In pt.Slicers
        cache.SlicerCache.ClearAllFilters
    Next cache
Next pt

End Sub

answered Mar 22 '16 at 14:13

mrbungle

1,921
1
16
27

That code doesn't work as they are PowerPivot slicers not Pivot Tables. The code I wrote works but takes around 25 secs to complete. I think this is because the data model is refreshing after each filter is cleared. Do you know a way to stop this refresh ? its driving me crazy ;-) – MartinHayes Mar 22 '16 at 16:29

Clearing Power Pivot Slicers

1 Answers1