Excel: Hide/Unhide Rows with VB

[pkuck]

I am trying to create a table that would hide a row based on the value from another row which is what I think makes it complicated.

I have a table that populates from another table where I input data (both on the same sheet). I have three rows where I input data 28-30. I want the code to look at column B in 28-30 and hide rows 10-12 if the values in 28-30 are zero. Row 10 populates from 28, 11 from 29 and 12 from 30. Therefore if 28 and 29 had a value of 1 in it and 30 had zero, only 12 would disappear.

I can get one row to hide with the code below, but not the rest because it keeps looking at 10. I'm guessing I need some sort of "next" statement.

Ideally, I would like to make this happen automatically (I hear "Events" will do that) and unhide the rows if there is a value other than zero. Also, I would like this to apply to multiple sheets of the same layout in the same workbook.

Am I asking too much of Visual Basic?



Sub HideRow()
'
' HideRow Macro
'
BeginRow = 28
EndRow = 30
ChkCol = 2

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

 

[SkipVought]

hi,

You have not declared your variables, not have you assigned RowCnt, so I have ignored that variable...

Sub HideRow()
'
' HideRow Macro
'
    Dim BeginRow As Long, EndRow As Long, ChkCol As Integer, rng As Range
    BeginRow = 28
    EndRow = 30
    ChkCol = 2
    
    Set rng = Range(Rows(BeginRow), Rows(EndRow))

    If Cells(1, ChkCol).Value = 0 Then
        rng.Hidden = True
    Else
        rng.Hidden = False
    End If
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[pkuck]

Skip,

Your code hides the rows where I am entering data. I don't want them hidden. I want row 10 hidden if row 28 column B has a zero value, row 11 hidden if 29 is zero and 12 hidden if 30 is zero.


I grabbed that original code off the web and modified it slightly. So I'm not real savvy on this stuff.