I'm trying to iterate through all of the available slicer items and remove only selected items (which may or may not be present in the available slicer items). This slicer data also comes from a data model. I'm trying the approach which was handled in this post, but I'm struggling to add the logic to handle multiple selection values to be deselected.
So if the slicer contains the values:
Pizza
Apple
Pear
Banana
Mango
...I'd like to only see Pizza and Mango selected.
Here's my code so far (nothing is deselected when this is run):
I suspect that this might be a syntax issue, but I can't figure it out. Thanks in advance!
So if the slicer contains the values:
Pizza
Apple
Pear
Banana
Mango
...I'd like to only see Pizza and Mango selected.
Here's my code so far (nothing is deselected when this is run):
Code:
Sub Test()
Dim slc As SlicerCache
Dim sli As SlicerItem
Dim iDic As Object
Set slc = ThisWorkbook.SlicerCaches("Slicer_Project_Name")
Set iDic = CreateObject("Scripting.Dictionary")
ActiveWorkbook.SlicerCaches("Slicer_Project_Name1").ClearManualFilter
For Each sli In slc.SlicerCacheLevels(1).SlicerItems
If sli.Value <> "apple" Then
iDic(sli.Name) = 1
ElseIf sli.Value <> "pear" Then
iDic(sli.Name) = 1
ElseIf sli.Value <> "banana" Then
iDic(sli.Name) = 1
End If
End If
Next
If iDic.Count = 0 Then
MsgBox "No item selected"
Else
slc.VisibleSlicerItemsList = iDic.Keys
End If
End Sub
I suspect that this might be a syntax issue, but I can't figure it out. Thanks in advance!
Last edited: