CODEWITHSUNDEEP
excelvbaslicers
Ronit
Ronit

Reputation: 1

Select multiple values from a slicer

I have a pivot table with a slicer. The slicer has 50+ values in it and these values can change. I use the slicer to filter the data - I am always looking for the same 5 values, and my 5 values start with the same text string.
I had recorded a macro to select my 5 values. this macro selects the items that are true and then lists all the other values and sets selection as false. When new values are added the macro crashes because I don't have an explicit line of code to set the selection of the new value to false. I found code to select one value from the slicer and deselect all other values without having to list them all explicitly, but I can't find code to find the 5 values and deselect all other values without having to list them explicitly, or a way to modify this code to select all slicer items that "contains" the consistent text string. either would help...

this is the code I have to find one specific value, but deselect the rest without stating them explicitly:

For Each slcCache In ActiveWorkbook.SlicerCaches
        slcCache.ClearManualFilter
Next

With ActiveWorkbook.SlicerCaches("Slicer_Fruit")
    For Each oSlicerItem In .SlicerItems
        If oSlicerItem.Name = "abcx Apple" Then
            oSlicerItem.Selected = True
        Else
            oSlicerItem.Selected = False
        End If
    Next oSlicerItem
End With

How can I change the code to select the slicer items that starts with "abcx" or contains this text? or is there a way to select "abcx Apple" and "abcx Pear" and "abcx Banana" but not select any other values?

Upvotes: 0

Views: 3145

Answers (1)

Display name
Display name

Reputation: 544

Try this :

Sub Slicer_select()

ActiveWorkbook.SlicerCaches("Slicer_Fruit").ClearManualFilter

Dim Sl_I As SlicerItem

For Each Sl_I In ActiveWorkbook.SlicerCaches("Slicer_Fruit").SlicerItems
    If Not Sl_I.Value Like "abcx*" Then Sl_I.Selected = False
Next

End Sub

Upvotes: 1

Related Questions