Avatar of AndresHernando
AndresHernando
 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
Microsoft Excel

Avatar of undefined
Last Comment
Rob Henson

8/22/2022 - Mon
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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question