MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Hiding Rows Given a Condition


Posted by Michael on June 13, 2001 7:17 PM

Is there a formula in Excel to hide rows given a specific value (if a1=0 then hide row)? If not, is there any other condition that would work?


Posted by steve w on June 13, 2001 7:57 PM

Yes, but you will have to use visual basic. You need to right click on the sheet tab then use some code simular to the following code below.
you can change = "PG2" to whatever you want
write numbers like this 2
text like this "text"
change sheets and ranges to suit
hope this helps
steve w

Private Sub Worksheet_Change(ByVal Target As Range)
If Sheet2.Range("C188") = "PG2" Then
Sheet2.Range("A36:A70").EntireRow.Hidden = False
Sheet3.Range("A36:A70").EntireRow.Hidden = False
Sheet4.Range("A36:A70").EntireRow.Hidden = False

Else
Sheet2.Range("A36:A70").EntireRow.Hidden = True
Sheet3.Range("A36:A70").EntireRow.Hidden = True
Sheet4.Range("A36:A70").EntireRow.Hidden = True

End If
End Sub

Posted by Joe Was on June 14, 2001 6:44 AM

The VB code below will hide any rows which contains zero in column A. It runs from a option Ctrl-+key now and was tested with values in column 1 and random values in its rows! JSW

Sub MyHide()
'This "Hides" any Row in Column(A) that =0.
'By J.S. Was, 6-12-2001.
Dim i
For i = 1 To 10
With Intersect(Columns(1), ActiveSheet.UsedRange)
Rows(i).Select
If .Rows(i).Value = 0 Then
Selection.EntireRow.Hidden = True
End If
End With
Next
End Sub