VBA Help: Row Hide/Unhide based on Slicer Count

[Slipp]

Hello there!

I am hoping to get some code for the following challenge:

I have a slicer on Sheet1 that controls a pivot on Sheet2, and the results are then read to a table on Sheet1. I have another Slicer on Sheet3 that controls a pivot on Sheet3. The field names are the same (Region) regardless of the slicer. I have 12 different Regions in that field. I would like to do a count of the number of selected items. If that item is 12, then hide a row. If the number of selected items is anything but 12, then I want to show the row (row 10). Here is some code that I had before, that works sometimes, but not for a particular Region for some reason:

Sub Hide_Unhide_facility()

Application.ScreenUpdating = False

Dim cache As Excel.SlicerCache
Dim sht As Worksheet
Dim sItem As Excel.SlicerItem

Set cache = ActiveWorkbook.SlicerCaches("Slicer_Region1")

For Each sItem In cache.SlicerItems

Rows(10).Hidden = sItem.Selected
Next

Application.ScreenUpdating = True
    
End Sub

The code above works for the first example (I have a slicer on Sheet1 that controls a pivot on Sheet2, and the results are then read to a table on Sheet1.) For the second example, one of the Regions (the last one) is not recognized - it hides the row no matter what. I am thinking to avoid this, I can do the count selected items solution I mentioned in the first part of my post, but I am open to any ideas on how to correct this.

Thank you so much for your help.

Excel 2016/Windows 10

 

[Slipp]

Ok, I have a solution.


On the sheet that has the pivot table, I have the following code:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

Application.ScreenUpdating = False

Const sSlicerName As String = "Region1"

        On Error Resume Next
        If Not IsError(Target.Slicers(sSlicerName).Name) Then _
            ActiveWorkbook.Worksheets("Worksheet 1 Name").Range("B125") = ActiveWorkbook.SlicerCaches("Slicer_" & sSlicerName) _
             .VisibleSlicerItems.Count

Call Hide_Unhide_facility

Application.ScreenUpdating = True

End Sub

Then in a module I have the following code:

Sub Hide_Unhide_Row()

Application.ScreenUpdating = False

Dim cell As Range
Set cell = Worksheets("Worksheet 1 Name").Range("B125")

If cell = 12 Then
Rows(10).Hidden = True
Else: Rows(10).Hidden = False
End If

Application.ScreenUpdating = True

End Sub

This is way simpler than what I was using before. It just counts the number of slicers that are selected, and if that number is 12, then it hides the row. I just have the calculated value directly on the sheet way below any of the data. I could no doubt combine these into just one macro at some point, but I've spent too much time on this already and I'm rolling with it.