VBA to clear all slicer filters

[Dokat]

Hi,

I have a very simple code where when i click i want VBA code to first clear all filters in the slicer then select RB. Below is the code i am using but no success. can anyone help?



ActiveWorkbook.SlicerCaches("Slicer_Manufacturer1").ClearManualFilter

With ActiveWorkbook.SlicerCaches("Slicer_Manufacturer1")
.SlicerItems("RB").Selected = True

End With
End Sub

 

[korhan]

You may do something like

Dim objSlicer As SlicerCache
For Each objSlicer In ActiveWorkbook.SlicerCaches
     If objSlicer.Name <> "RB" Then objSlicer.ClearManualFilter
Next objSlicer

I am thinking why would you leave "RB" out and clear all the others. I have not tried this code so don't know if it works, especially objSlicer.Name portion.
I am just trying to give you an idea.

 

[Dokat]

Hi Korhan,

Code cleared all the filters in 8 slicers but under Maufacturer1 slicer it didnt select RB. Can you please help.

Thanks

 

[korhan]

Well, try this. I am not how your data is named under your slicers. I have tested this on my computer and it works.

Sub SlicerTest()


    Dim objSlicer As SlicerCache
    
    For Each objSlicer In ActiveWorkbook.SlicerCaches
         If objSlicer.Name <> "RB" Then objSlicer.ClearManualFilter
    Next objSlicer
    
    ' Select a certain filter on a slicer
    With ActiveWorkbook.SlicerCaches("Slicer_Manufacturer1")
        
        For i = 1 To .SlicerItems.Count
            If .SlicerItems(i).Caption Like "RB" Then
                .SlicerItems(i).Selected = True
            Else
                .SlicerItems(i).Selected = False
            End If
        Next i
    End With
    
End Sub

 

[Dokat]

This is what i tried and still couldn't get it to work. It clears all filters but not selecting Reckitt Benckiser under Manufacturer1 slicer.

Sub RB()




Dim objSlicer As SlicerCache

For Each objSlicer In ActiveWorkbook.SlicerCaches
If objSlicer.Name <> "Reckitt Benckiser" Then objSlicer.ClearManualFilter
Next objSlicer

' Select a certain filter on a slicer
With ActiveWorkbook.SlicerCaches("Slicer_Manufacturer1")

For i = 1 To .SlicerItems.Count
If .SlicerItems(i).Caption Like "Reckitt Benckiser" Then
.SlicerItems(i).Selected = True
Else
.SlicerItems(i).Selected = False
End If
Next i
End With

End Sub

 

[korhan]

Hey there,

Sorry for not getting back to you before. I was really busy. Were able to solve this issue?

 

[Dokat]

Hi Korhan,

yes I was able to get it to work. Thanks for your help. My last question is there a way to speed up the selection process...I have too many manufacturers listed and it takes very long time to run the code.

Thanks

 

[korhan]

Check this thread and let me know if it helps or not
Poor performance updating Excel slicer selection using VBA - Stack Overflow