asked on

Hide rows that contain a specific value

My code is too slow. Is there a faster way to hide rows for a range based on the value of the cells?
I want to hide all the rows in myRange when the cell value = 1

This is what I have now:

For Each cCell In Range("myRange")
If cCell.Value = 1 Then
cCell.EntireRow.Hidden = True
Else
cCell.EntireRow.Hidden = False
End If
Next cCell

Martin Liss

Does this help?

Application.ScreenUpdating = False
For Each cCell In Range("myRange")
            If cCell.Value = 1 Then
                cCell.EntireRow.Hidden = True
                Else
                cCell.EntireRow.Hidden = False
            End If
        Next cCell
Application.ScreenUpdating = True

Open in new window

AndresHernando

ASKER

Martin, thanks for the suggestion but the problem is the looping. I developed a work-around by using xlSpecialCells.

Thanks, --Andres

AndresHernando

ASKER

I named a range next to the one in question in which I put in blanks for the rows to hide then ran the code:

Range("myRangeWithBlanks").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True

Now there's no looping and the procedure hides the rows in an instant.

AndresHernando

ASKER

I've requested that this question be closed as follows:

Accepted answer: 0 points for AndresHernando's comment #a38448940

for the following reason:

Expert solution still looped which made it too slow.  My solution overcame this and is very fast.

ASKER CERTIFIED SOLUTION

Rob Henson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial