VBA to deselect multiple slicer items using data model pivot table

[sneeekey]

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):

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!

 

[sneeekey]

Thanks, but that ended up selecting the two slicer values Pizza and Mango, and deselecting everything else.

The following ended up working for me. Hopefully it helps someone else who comes across this issue:

Sub Test()

Dim slc As SlicerCache
Dim sli As SlicerItem
Dim iDic As Object

Set slc = ThisWorkbook.SlicerCaches("Slicer_Project_Name")
Set SL = slc.SlicerCacheLevels(1)
Set iDic = CreateObject("Scripting.Dictionary")
vSelection = Array("pizza", "mango")

ActiveWorkbook.SlicerCaches("Slicer_Project_Name").ClearManualFilter
 
For Each sli In SL.SlicerItems
    On Error Resume Next
    If IsInArray((CStr(sli.Value)), vSelection) Then
            iDic(sli.Name) = 1
    End If
    On Error GoTo 0
Next

    If iDic.Count = 0 Then
        MsgBox "No item selected"
    Else
        slc.VisibleSlicerItemsList = iDic.Keys
                MsgBox "Deselected!"
    End If
End Sub

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
    IsInArray = IsError(Application.Match(stringToBeFound, arr, 0))
End Function

 

[lkld999]

It looks good. But it actually does SELECT, NOT DESELECT. I needed to do DESELECT of two specific items. the following code end up working for me. Hope it is useful for others. I needed to DESELECT "Jan", "Feb", select the rest of the months as the time goes.

>>> use code - tags <<<

Sub SetSlicer()
Dim slc As SlicerCache
Dim sli As SlicerItem
Dim iDic As Object
Set slc = ThisWorkbook.SlicerCaches("Slicer_MONTH_CLOSED")
Set iDic = CreateObject("Scripting.Dictionary")

For Each sli In slc.SlicerCacheLevels(1).SlicerItems
    'MsgBox (sli.Value)
  
    If sli.Value <> "Jan" And sli.Value <> "Feb" Then
        iDic(sli.Name) = 1 'adding to visible
      '  MsgBox (iDic(sli.Name))
    End If
  

Next

'make sure iDic.Count is not 0, which means there are items selected

 slc.VisibleSlicerItemsList = iDic.Keys
End Sub