PDA

View Full Version : Conditionally hide and unhide cell ranges



austenr
08-05-2010, 09:50 AM
I need to conditionally hide and unhide ranges of cells based on a certain condition. I know it goes in the worksheet change event.

Found this code that does rows, but how do you alter it to do say C1:C15 and F1:F15 if the condition is met?

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$C$14" Then
If Target.Value = 1 Then
Rows(10).EntireRow.Hidden = True
Else
Rows(10).EntireRow.Hidden = False
End If
End If
End Sub

Bob Phillips
08-05-2010, 09:54 AM
You can't hide individual cells, it is entire rows/columns, or nothing.

austenr
08-05-2010, 09:58 AM
I was afraid of that Bob. I have borders around cells that if the rows are hidden, the remaining will look bloody awful.

YasserKhalil
08-05-2010, 09:59 AM
Try this code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Union(Range("C1:C15"), Range("F1:F15"))
If Not Intersect(Target, rng) Is Nothing Then
If Target.Value = 1 Then
Rows(10).EntireRow.Hidden = True
Else
Rows(10).EntireRow.Hidden = False
End If
End If
End Sub

austenr
08-05-2010, 10:13 AM
May be able to use the first code after all. how do you do multiple rows?