VBA Code not hiding and unhiding rows based Cell C25 Updates

Hi all,

I’ve created a VBA script with the goal of dynamically hiding rows in my worksheet based on the value in cell C25. This cell is updated via a formula that counts the number of visible rows in PivotTable1 based on the slicers applied. For example, if C25 equals 29, the code should hide any blank rows starting at row 30 up to Table1. Currently, PivotTable1 ends at row 89, but this can change as more rows are added. Table1 starts at row 110, and I have additional title headers at row 105 that are not part of the table.

Ideally, I want the script to hide rows dynamically up to row 105, while also accounting for any potential changes in the number of rows in PivotTable1. However, the script isn’t working as intended. Here’s what happens:

1. Initially, if C25 equals 29, the script hides rows starting from row 30 onward as expected.
2. If I update the slicer (e.g., add or remove filters), the script does not adjust properly. For instance, if the slicer changes to display more or fewer rows in PivotTable1 (e.g., C25 updates to a different value like 26 or 35), the script does not unhide or hide rows dynamically to reflect these changes.

I also originally tried to trigger this script whenever I selected or deselected options in my slicer named "Point Name," but I couldn’t get that approach to work either.

Could anyone help me refine the script to ensure the rows adjust dynamically whenever the slicer is updated?

This is my VBA code: 

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim tblStartRow As Long
    Dim lastDataRow As Long
    Dim currentRow As Long

    On Error GoTo EnableEvents ' Ensure events are re-enabled on error

    ' Define the worksheet
    Set ws = Me ' Refers to the current worksheet

    ' Check if the change occurred in C25
    If Not Intersect(Target, ws.Range("C25")) Is Nothing Then
        Application.EnableEvents = False ' Prevent triggering this event again

        ' Get the value in C25
        lastDataRow = ws.Range("C25").Value

        ' Debugging output
        Debug.Print "C25 value: " & lastDataRow

        ' Define Table1 and its start row
        On Error Resume Next
        Set tbl = ws.ListObjects("Table1") 
        If tbl Is Nothing Then
            MsgBox "Table1 not found. Check the table name.", vbCritical
            GoTo EnableEvents
        End If
        On Error GoTo 0
        tblStartRow = tbl.Range.Rows(1).Row

        ' Debugging output
        Debug.Print "Table1 starts at row: " & tblStartRow

        ' Unhide all rows first
        ws.Rows("25:" & tblStartRow - 1).Hidden = False

        ' Validate that C25 contains a valid number and is within range
        If IsNumeric(lastDataRow) And lastDataRow >= 25 And lastDataRow < tblStartRow Then
            ' Debugging output
            Debug.Print "Valid C25. Hiding rows from " & (lastDataRow + 1) & " to " & (tblStartRow - 1)

            ' Hide rows after lastDataRow up to the row before Table1
            ws.Rows((lastDataRow + 1) & ":" & (tblStartRow - 1)).Hidden = True

            ' Debugging output
            Debug.Print "Rows " & (lastDataRow + 1) & " to " & (tblStartRow - 1) & " are now hidden."
        Else
            ' Debugging output
            Debug.Print "Invalid C25 value. Unhiding all rows in range."
            MsgBox "C25 contains an invalid value or is out of range.", vbExclamation
        End If

    End If

EnableEvents:
    Application.EnableEvents = True ' Re-enable events
End Sub

Thanks in advance!