Excluding Multiple Slicers when clearing all slicers using VBA

Question

I have a set of slicers that I need to clear, excluding 2 slicers which need to remain as whatever is selected. The code I am currently using allows me to exclude 1 of the slicers from the clear, but I cannot find the correct way to exclude the 2nd slicer. The code below is what I'm using:

Sub Clearslcr()
    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, "Measure") = False Then
                    Slcr.ClearManualFilter
                End If
            End If
        Next SL
    Next Slcr
End Sub

Can anybody please advise how to make it exclude both? I'm trying to exclude "Measure" and "Current vs Comparison", seem to be able to do one or the other! (New to using VBA so learning as I go!)

`If Not (InStr(SL.Name, "Measure")) And Not (InStr(SL.Name, "Current vs Comparison")) Then` — Vityata, May 04 '18 at 13:19
Hiya, appreciate the above, this now removes all slicers on the sheet, I've checked and the code matches the slicer name but seems to be removing it all. — M Bennett, May 04 '18 at 13:30
Play a bit, it can be that you need `Or` instead of `And` :) Remove the `Not` as well - just guessing - `If InStr(SL.Name, "Measure") or InStr(SL.Name, "Current vs Comparison")` — Vityata, May 04 '18 at 13:32
Or now only clears the slicer for "Current vs Comparison". And seems to be clearing all slicers :( Will keep playing with the combindation — M Bennett, May 04 '18 at 13:50
Can you do it manually and `record a macro`? Probably the clearing should be done at once, idk ... — Vityata, May 04 '18 at 13:52
It works! Needed to add another [End If] if after adding the additional command in to make it work! Thanks for your help :) — M Bennett, May 04 '18 at 13:54
@MBennett If possible, please consider adding your solution as an answer below and then click the checkmark next to it to let future readers know what solved your problem! — TylerH, May 04 '18 at 14:33

score 0 · Answer 1 · answered May 04 '18 at 13:21

If you want to exclude 2 variables from a for-loop a sample like this can be of use:

Sub TestMe()

    Dim bar, foo
    bar = Array(1, 2, 3, 4, 5)

    For Each foo In bar
        If Not (foo = 1) And Not (foo = 2) Then
            Debug.Print foo
        End If
    Next foo

End Sub

It prints 3,4,5, as far as 1 and 2 are ignored.

Excluding Multiple Slicers when clearing all slicers using VBA

1 Answers1