Macro to hide multiple rows

M

Minh

Hi,

I am writing a macro at the moment that hides a row given
a certain criteria is met. However, at the moment I have
had to do this on a row by row basis:

e.g.
If A8 = Cat Then
Rows("8:8").Select
Selection.EntireRow.Hidden = True
End If
If A9 = Cat Then
Rows("9:9").Select
Selection.EntireRow.Hidden = True
End If

etc....

Is there a way to do this to a range of cells rather than
on a cell by cell basis?

Thanks heaps.
 
H

Harald Staff

Hi

You will have to loop cell by cell for this, but it's easy and fast:

Sub test()
Dim R As Range, Cel As Range
Set R = Range("A1:M300")
For Each Cel In R
If Cel.Value = "Cat" Then _
Cel.EntireRow.Hidden = True
Next
End Sub

HTH. Best wishes Harald
 
J

JulieD

alternatively, you might want to look into using filtering for this ... if
you use the macro recorder and data / autofilter .. and use the custom
option to display only those rows not equal to cat - it might give you what
you want.

Cheers
JulieD
 
Top