Automatically Show/Hide Row based on cell value

  • Thread starter Thread starter plantechbl
  • Start date Start date
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
 
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?
 
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
 
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
 
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
 
Back
Top