hide sheet rows based on slicer selection

Question

Is there any way to hide certain rows if a certain value in slicer is selected? I have some plots that need to be shown only if one specific chain is selected, if it's not selected - then hide the plots (located in rows 287:345). I tried following, but it didn't work:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If ActiveWorkbook.SlicerCaches("Slicer_Chain").SlicerItems("ChainName").Selected = True Then
        Rows("287:346").Hidden = False
    Else
        Rows("287:346").Hidden = True
    End If

End Sub

Answer 1

Requirement: To show\hide a Range of rows based on the Selected status of a SlicerItem.

VBA Procedure: (as per OP approach)

Try the following procedure (see comments\explanation within the procedure):

    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim sc As SlicerCache, sl As Slicer
    Dim sPt As String, sFld As String, sItm As String, sRng As String

    'Use variables to hold the criteria to be applied
        sPt = "PivotTable1"
        sFld = "Chain"
        sItm = "A.6"
        sRng = "287:346"

        With Target

            Rem Validate PivotTable
            If .Name <> sPt Then Exit Sub

    ' As Slicer names can be easily changed by users, need to identify
    ' the SlicerCache connected to the target `PivotTable` using the 
    ' SourceName of the PivotField. This step returns the SlicerCache 
    ' connected to the PivotTable otherwise, the SlicerCache is nothing.
            Rem Set SlicerCache
            For Each sl In .Slicers
                If sl.SlicerCache.SourceName = sFld Then
                    Set sc = sl.SlicerCache
                    Exit For
        End If: Next: End With

        Rem Validate SlicerItem & Apply Result
        If Not (sc Is Nothing) Then

    ' This line Shows\Hides the target range based on the opposite 
    ' status of the target SlicerItem.
            Me.Rows(sRng).EntireRow.Hidden = Not (sc.SlicerItems(sItm).Selected)

        Else

    ' PivotTable is not connected to a Slicer of the target PivotField
            MsgBox "PivotTable [" & sPt & "]" & String(2, vbLf) & _
                vbTab & "is not connected to Slicer of Field [" & sFld & "].", _
                vbApplicationModal + vbInformation, "Slicers Selection"

        End If

        End Sub

Another Aproach:

Bear in mind that a Slicer is a kind of remote control of the PivotTables that works in both directions, i.e. the Slicer updates the PivotTables and the PivotTables update the Slicer, as such there is no need to validate if the PivotTable was updated by a Slicer operation, just need to check the Visible property of the target PivotItem in the target PivotTable, regardless of whether the Slicer and the PivotTable are connected or not.

This procedure only uses the target PivotTable i.e. shows or hides the target range, based on whether the PivotItem is visible or not in the PivotTable.

    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim sPt As String, sFld As String, sItm As String, sRng As String

        sPt = "PivotTable1"
        sFld = "Chain"
        sItm = "A.6"
        sRng = "287:346"

        With Target

            Rem Validate PivotTable
            If .Name <> sPt Then Exit Sub

            Rem Validate PivotItem & Apply Result
            With .PivotFields(sFld).PivotItems(sItm)
                Me.Rows(sRng).EntireRow.Hidden = Not (.Visible)

        End With: End With

        End Sub