VBA to hide SlicerItems

Question

I have a slicer that contains items from a pivot table. My users wants the ability to hide certain slicer items even though these items contains data. Is there a way to hide sliceritems via VBA code?

Answer 1

Why not just filter them out? If this is an OLAP PivotTable, see Pivot Table filter out only 1 option

If this is a "Traditional" PivotTable, then you can use a variation of the code I posted at Pivotfields multiple filter

Here's some amended code:

Sub FilterSlicer_Inverse()
Dim slr As Slicer
Dim sc As SlicerCache
Dim si As SlicerItem
Dim i As Long
Dim vItem As Variant
Dim vSelection As Variant
Dim pt As PivotTable

Set sc = ActiveWorkbook.SlicerCaches("Slicer_test")
vSelection = Array("1", "2") <= List the items you want to hide in here
For Each pt In sc.PivotTables
    pt.ManualUpdate = True 'Stops PivotTable from refreshing after each PivotItem is changed
Next pt

With sc
    .ClearAllFilters
    On Error Resume Next 'In case one of the items isn't found
    For Each vItem In vSelection
        .SlicerItems(vItem).Selected = False
    Next vItem
    On Error GoTo 0
End With

For Each pt In sc.PivotTables
    pt.ManualUpdate = False
Next pt

End Sub

Note that it doesn't matter if you change the PivotItems or the SlicerItems...you get the same result.