CODEWITHSUNDEEP
excelvbapivot-table
ARich
ARich

Reputation: 3279

How to make 'Clear Slicers' Code More Efficient?

I'm working with six slicers on a summary sheet in Excel 2010. Each slicer is connected to 26 pivot tables.

I used the code below in the past to clear slicer selections and it worked with minimal lag. This time the code takes 6-7 seconds to execute. 

Dim oSlicerC As SlicerCache

For Each oSlicerC In ActiveWorkbook.SlicerCaches
    oSlicerC.ClearManualFilter
Next oSlicerC

I can't find anything online to speed up the macro/make it more efficient.

I tried setting ScreenUpdating, DisplayAlerts, and EnableEvents to False and I tried setting Calculation to xlCalculationManual at the beginning of my code.

I thought about iterating through each SlicerItem to de-select each one, but with up to 100 items in a few slicers I'm not sure that would be any faster.

I'm not looking for code as I'd like to take a stab at it myself, but I'm not sure which path I should take or if there's even a more efficient alternative to what I'm already using.

Upvotes: 1

Views: 20686

Answers (3)

nir
nir

Reputation: 1

i see it was posted long ago, but still this could be usefull: i just defined 2 parameters for sliceritems, and while looping on first items, ran another loop to unselect all. '''

For Each oSi In oScR.SlicerItems
    If oSi.HasData Then
        For Each oSiT In oScR.SlicerItems:
            If oSiT.HasData Then oSiT.Selected = False
        Next
        oSi.Selected = True

    End If
Next

'''

Upvotes: 0

Bella O.
Bella O.

Reputation: 43

It's an old post but there is also another possibility.

If you have the data in an excel table you can call on that table

tbl.AutoFilter.ShowAllData

This will also reset all slicers very fast.

with 173'561 rows and other things in between my program.

for each example above: 6.88 sec my example: 6.92 sec

Perhaps there are situation where one or the other is faster. e.g. it has to reset more than just a few slicers my version should be faster.

Upvotes: 0

Nutley
Nutley

Reputation: 41

I had a similar problem, settled on:

Dim oSlicerC As SlicerCache
For Each oSlicerC In ActiveWorkbook.SlicerCaches
If oSlicerC.FilterCleared = False Then oSlicerC.ClearManualFilter
Next oSlicerC`

Upvotes: 4

Related Questions