Hide multiple non-sequential rows simultaneously based on condition of cell

Question

I am looking for a way to simultaneously hide multiple, non-sequential rows based on a value in the rows I want to hide.

In each worksheet there is a single column named range and each cell in the range contains the value 1 or 2, depending on some other calculation. I then want to hide those rows whose value in the range contain the value 2.

The key word of my question is "simultaneously". I have code that will do exactly what I describe, but it does it one row at a time. There are over 20,000 rows and this can take quite some time.

I figure that if it just hid all of the cells that have a "2" (or that don't contain the number "1") at the same time it would be substantially faster (I hope I'm right).

Here's the code I am currently using. The code is set up to be called by various different macros in the model, each pointing to a different named range, where strRange represents the name of the range that will be checked for 1 & 2's. The named ranges (strRange) are in different worksheets. Rather than repeat the code over and over I simply created this one set of code to be used repeatedly.

Sub ExpandHideRows(strRange As String)

    Dim ExHR As Range 
     
    Set ExHR = Range(strRange) 
    ExHR.EntireRow.Hidden = False 'code first expands all rows prior to hiding rows containing value "2"
     
    For Each ExHR In Range(strRange) 
        If ExHR.Value = 2 Then 
            ExHR.EntireRow.Hidden = True 
        End If 
    Next ExHR 
     
    Set ExHR = Nothing 
     
End Sub 

Thanks for the help.

Answer 1

Hiding only once will make the code much faster:

Sub ExpandHideRows(strRange As String)

Dim ExHR As Range

Dim rHide As Range: Set rHide = Nothing

Set ExHR = Range(strRange)

ExHR.EntireRow.Hidden = False 'code first expands all rows prior to hiding rows containing value "2"

For Each ExHR In Range(strRange)

If ExHR.Value = 2 Then

If rHide Is Nothing Then

Set rHide = ExHR

Else

Set rHide = Union(ExHR, rHide)

End If

End If

Next ExHR

If rHide Is Nothing Then

Else

rHide.EntireRow.Hidden = True

Set ExHR = Nothing

End If

End Sub

Answer 2

Thanks for the feedback!

Answer 3

Gary, excellent!

This is exactly what I was hoping and looking for.

What used to take over 5 minutes is now being done in 6 seconds.

Much appreciated.