Hide Row command using if statement
 

Is there a way to hide a row based on if a value exists in the row. For
example if I have values in rows 2-40 all in column A. I enter a value of
27 in cell A1. I would like all the rows with values of 28 or higher to be
hidden. Is this possible?

Hi,
this is achievable by using a Worksheet_Change Event

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim objCell As Range
If Target.Address = "$A$1" Then
Me.Cells.EntireRow.Hidden = False
For Each objCell In Me.Range(Cells(2, 1), Cells(65536, 1).End(xlUp))
If objCell.Value < Target.Value Then
objCell.EntireRow.Hidden = True
End if
Next objCell
End If
End Sub


Putting 0 in the cell A1 will unhide all rows (provided there are no
negative values in your column)

Hth,
O

--
Message posted via http://www.officekb.com

This worksw great. Just one other question/revision needed. Is there a way
to get this to work if Cell $A$1 is linked to another cell. Here is waht I
am trying to do. The user will enter the number of years, this will then
generate the number of quarters in that time span. So if the user enter 5
there are 60 quarters(periods) in that span. So in cell $a$1 ihave the cell
taking the number of years entered multipied by 4. When the cell changes,
the rows will not hide. Is there a way to get this to work?

Thanks for any help.


"Oliver Ferns via OfficeKB.com" wrote in message
...
Hi,
this is achievable by using a Worksheet_Change Event

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim objCell As Range
If Target.Address = "$A$1" Then
Me.Cells.EntireRow.Hidden = False
For Each objCell In Me.Range(Cells(2, 1), Cells(65536, 1).End(xlUp))
If objCell.Value < Target.Value Then
objCell.EntireRow.Hidden = True
End if
Next objCell
End If
End Sub


Putting 0 in the cell A1 will unhide all rows (provided there are no
negative values in your column)

Hth,
O

--
Message posted via http://www.officekb.com