Hiding rows

[Guest]

We would like to be able to enter a value in a cell and if it meets our
predefined criteria the entire row would automatically hide. Is this possible
in excel?

Thanks

 

[Chip Pearson]

You can use and event procedure to do this (see
http://www.cpearson.com/excel/events.htm for more info about
events). Right click the sheet tab and choose View Code. In the
VBA code module that appears, paste the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then 'change cell as desired
If Target.Value > 10 Then ' change criteria as desired
Target.EntireRow.Hidden = True
Else
Target.EntireRow.Hidden = False
End If
End If

End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


message
news:[email protected]...

 

[Guest]

Thank you Chip

You can use and event procedure to do this (see
http://www.cpearson.com/excel/events.htm for more info about
events). Right click the sheet tab and choose View Code. In the
VBA code module that appears, paste the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then 'change cell as desired
If Target.Value > 10 Then ' change criteria as desired
Target.EntireRow.Hidden = True
Else
Target.EntireRow.Hidden = False
End If
End If

End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


message

 

[Guest]

Chip,

How would you set this to a range of cells in column A

 

[Chip Pearson]

David,


Change

If Target.Address = "$A$1" Then

to

If Not Application.Intersect(Target, Range("A1:A100")) Is
Nothing Then


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




message

 

[Dave Peterson]

One way...

Private Sub Worksheet_Change(ByVal Target As Range)
if target.cells.count > 1 then exit sub
if intersect(target,me.range("a1:a25")) is nothing then exit sub

If Target.Value > 10 Then ' change criteria as desired
Target.EntireRow.Hidden = True
Else
Target.EntireRow.Hidden = False
End If

End Sub

 

[Guest]

Thank You Chip. That was perfect. Your website is a great source of
information.

David Curlis