How to Automatically Hide/Unhide Rows Based on Cell Value

[jamber17]

I have a worksheet where column A data changes when you select a different drop down option (ex - hours, weekdays, months, etc.). But I don't want the blank rows to show. For example, there are currently 53 rows for the 53 weeks in a year, but when I switch the data to Weekdays I want it to only show the 7 rows for the 7 weekdays. I don't want this to be a filter but a recurring VBA code that automatically updates when the data type changes (cell B5). I've attached a picture and the current code that I'm using, but the code isn't changing automatically.

Sub HURows()
BeginRow = 9
EndRow = 60
ChkCol = 1

For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = 0 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
End Sub

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim RowCnt As Long
   
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "B5" Then
   For RowCnt = 9 To 60
      Rows(RowCnt).Hidden = Cells(RowCnt, 1).Value = 0
   Next RowCnt
End Sub

This needs to go in the relevant sheet module.

 

[Fluff]

Oops missed the end if, it should be

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim RowCnt As Long
   
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "B5" Then
      For RowCnt = 9 To 60
         Rows(RowCnt).Hidden = Cells(RowCnt, 1).Value = 0
      Next RowCnt
   End If
End Sub

 

[jamber17]

For some reason it's not working. I'm super new to VBA, so can you tell me if I'm doing something wrong? Here's the error I keep getting.

 

[Fluff]

You need to delete that code from the module & put it into the Sales & Penetration Dashboard module.
Then when you manually change B5 the rows will (un)hide