Forum Discussion
jazzyelsie
Jan 08, 2025Copper Contributor
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 row...
HansVogelaar
Jan 08, 2025MVP
Worksheet_Change is not triggered by cells containing a formula. You'd need to use the Worksheet_Calculate event. Unfortunately, this does not have a Target argument, so you cannot check whether C25 has changed. It would look like this:
Private Sub Worksheet_Calculate()
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
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
EnableEvents:
Application.EnableEvents = True ' Re-enable events
End Sub