Automatically Show/Hide Row based on cell value

P

plantechbl

I need to have a row hidden or revealed based on a value in cell A16.

I have this code in the worksheet which works fine EXCEPT it apparently
resets the Undo stack and I don't have any undo capabilities in any
workbook that is open at the time.

I simply need to hide or reveal a row based on the cell value in A16
being "None". Any help either with this code or a totally different
approach will be greatly appreciated.

Private Sub Worksheet_Calculate()
Dim rCell As Range
For Each rCell In Range("a16")
rCell.EntireRow.Hidden = (rCell.Value = "None")
Next rCell
End Sub
 
D

Don Guillett

Some clarification is needed. Perhaps you want to hide all cells in a larger
range if they have the same text as cell a16?
or
do you want to hide a16:a222 if they have none?
See, if you hide a16 only if it has none then how do you unhide it?
 
P

plantechbl

Don,
Cell A16 is linked to another cell which is always visible, so when the
data entry cell C15="None" A16 will="None". As C15 value changes A16
will change and reveal the row. In this manner I can control the
visibility of many rows based on the value of a single always visible
value.

Thanks for such a prompt reply!
Bill
 
D

Don Guillett

then try this to hide row 16 when cell a15 changes

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$15" Then Exit Sub
If UCase(Target) = "NONE" Then
Rows(16).Hidden = True
Else
Rows(16).Hidden = False
End If
End Sub
 
P

plantechbl

Don,
I got it to work fine if I use the value in the data entry cell C15
(and I have Undo back again).
My final code looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$15" Then Exit Sub
If UCase(Target) = "NONE" Then
Rows(16).Hidden = True
'Rows(18).Hidden = True
Else
Rows(16).Hidden = False
'Rows(18).Hidden = False
End If
End Sub

Thank you very much for your help,
Bill
 
Top