Reset Slicer VBA takes too long

[melq64]

Hi,

Am currently the code below to Reset Slicers on a worksheet and it works fine but takes too long (30sec+). Quicker to do it manually.

Is there any way to optimize code to run faster??

Appreciate any help


Private Sub ClearAllSlicers_Click()


Dim slcr As SlicerCache
Dim sl As Slicer




For Each slcr In ActiveWorkbook.SlicerCaches
For Each sl In slcr.Slicers
If sl.Parent.Name = ActiveSheet.Name Then

If InStr(sl.Name, "FILTER DATE") = False Then




slcr.ClearManualFilter
End If
End If
Next sl
Next slcr
End Sub

 

[JackDanIce]

Hi, two untested suggestions, the second one may be faster, but less confident the code or syntax is correct in either; test on a copy of your workbook and try:

Suggestion #1

Private Sub ClearAllSlicers_Click()
    
    Dim slcr As SlicerCache
    Dim sl As Slicer
        
    Application.ScreenUpdating = False
        
    For Each slcr In ActiveWorkbook.SlicerCaches
        For Each sl In slcr.Slicers
            With sl
                If .Parent.Name = ActiveSheet.Name And InStr(.Name, "FILTER DATE") = 0 Then slcr.ClearManualFilter
            End With
        Next sl
    Next slcr
    
    Application.ScreenUpdating = True
    
End Sub

Suggestion #2

Private Sub ClearAllSlicers_Click()
    
    Dim x       As Long
    Dim y       As Long
    
    Application.ScreenUpdating = False
            
    With ActiveWorkbook.SlicerCaches
        For y = 1 To .count
            With .Item(y)
                For x = 1 To .Slicers.count
                    With .Slicers(x)
                        If .Parent.Name = ActiveSheet.Name And InStr(.Name, "FILTER DATE") = 0 Then ActiveWorkbook.SlicerCaches.Item(y).ClearManualFilter
                    End With
                Next x
            End With
        Next y
    End With
    
    Application.ScreenUpdating = True
    
End Sub

 

[melq64]

Thanks for the suggestions. Both working and slightly quicker than the original but still too slow (20sec).

Any further suggestions?

 

[JackDanIce]

You mean an efficiency gain of 33% isn't enough?! lol Not worked with slicers so just trying suggestions that generally lower execution time

E.g.
Single line IF statement if there's only a single action (and less code to compile and execute)
Using the index count to iterate over each item, rather than using an object variable against FOR EACH
Using WITH statements for objects
Disabling screen updating

If you have calculations in your workbook, you can try disabling and then re-enabling calculation mode, so the workbook is not updating calculations whilst the code runs.

Adjusting suggestion #2 for this:

Private Sub ClearAllSlicers_Click()
    
    Dim x       As Long
    Dim y       As Long
    
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
            
    With ActiveWorkbook.SlicerCaches
        For y = 1 To .count
            With .Item(y)
                For x = 1 To .Slicers.count
                    With .Slicers(x)
                        If .Parent.Name = ActiveSheet.Name And InStr(.Name, "FILTER DATE") = 0 Then ActiveWorkbook.SlicerCaches.Item(y).ClearManualFilter
                    End With
                Next x
            End With
        Next y
    End With
    
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
    
End Sub

 

[melq64]

Truly appreciate your help

Your latest suggestion worked but took same time as previous suggestions. Sorry

Guess I'll just have to put this feature in the "nice to have basket"

 

[JackDanIce]

No problem Interested to see if anyone makes suggestions to improve further, but without your workbook and based on the code solely, afraid I can't think of any other suggestions.