Speed Up Macro that Hides Rows

[ker9]

Hi - this macro works but it seems slow. Any suggestions for speeding it up? It's attached to a checkbox.

Application.ScreenUpdating = False

    With ThisWorkbook.ActiveSheet
    
        Dim i As Long
        Dim lr As Long
        lr = Cells(Rows.Count, 1).End(xlUp).Row
        
        For i = 1 To lr
            If Range("D" & i).Interior.Color = RGB(217, 217, 217) Then
                Range("D" & i).EntireRow.Hidden = True
            End If
        Next
     
    End With

Application.ScreenUpdating = True

It also doesn't take into consideration any rows that might be hidden already, however, that shouldn't happen but probably could.
I have the same basic code to unhide the rows.

Thank You.

[judgeh59]

I've never really tested this, but when I deleted or hide rows I usually do this

For i = lr to 1 step - 1

I don't know if it would be faster....but hey, it's worth a shot...

HTH

[ker9]

Hi JudgeH59 - I tried your suggestion but didn't notice any appreciable increase or decrease in speed. Thank you for trying!

[stnkynts]

Autofilter will probably be one of the fastest options. Assuming autofilter is an acceptable solution for "hiding" the rows.

Sub AutoFilterbyColors()
Dim ws As Worksheet:    Set ws = ActiveSheet

With ws
    .AutoFilterMode = False
    .Range("D1:D" & .Range("D" & Rows.Count).End(xlUp).Row).AutoFilter 1, RGB(217, 217, 217), xlFilterCellColor
End With

End Sub

[stnkynts]

Are you referring to my suggestion? Our posts were about the same time

[ker9]

Hi stnkynts - thank you for your response, but autofilter won't work for this application.

[judgeh59]

so a few question that are standard for "Slow" stuff

1) do you run conditional formatting?....that can really slow stuff down
2) are you doing a lot of calculations?....like Array formulas
3) how long is it actually taking?....how many rows and columns in the worksheet.,
4) how many worksheets?

[1aaaaaaaaaaaaaaa]

Hello! Try this.

Sub Hide_Row()

    Application.ScreenUpdating = False

    With ThisWorkbook.ActiveSheet
    
        Dim i As Long
        Dim lr As Long
        lr = Cells(Rows.Count, 1).End(xlUp).Row
        
        Debug.Print lr
        
        Dim rRange As Range
        
        For i = 1 To lr
            
            If Range("D" & i).Interior.Color = RGB(217, 217, 217) Then
                
                If rRange Is Nothing Then
                
                    Set rRange = Range("D" & i).EntireRow
                
                Else
                    
                    Set rRange = Union(rRange, Range("D" & i).EntireRow)
                
                End If
            
            End If
            
        Next
     
    End With
    
    rRange.EntireRow.Hidden = True

    Application.ScreenUpdating = True

End Sub

[ker9]

Poizhan: WOW!! - that is a lot faster! Not sure I understand why - perhaps you can explain? THANK YOU!

judgeH59: +/- 300 rows and 5 columns, no conditional formatting and no calculations - it's all text.

All: after struggling with this I decided to shut down everything and restart the PC to ensure there wasn't anything hogging memory. After doing that, everything seemed to work faster but Poizhan's answer is faster yet. Thank you!

[1aaaaaaaaaaaaaaa]

Poizhan: WOW!! - that is a lot faster! Not sure I understand why - perhaps you can explain? THANK YOU!

judgeH59: +/- 300 rows and 5 columns, no conditional formatting and no calculations - it's all text.

All: after struggling with this I decided to shut down everything and restart the PC to ensure there wasn't anything hogging memory. After doing that, everything seemed to work faster but Poizhan's answer is faster yet. Thank you!

While iterating, instead of each pass performing the hide action (this is what causes the overhead) by row, you can create an instance of a range object, on the first pass that meets the IF criteria, you will set it to that range, on each subsequent pass that meets the IF criteria, you will Union that range to the existing range (analogous to creating an array of range pointers) and then once your FOR loop is finished, you perform the hide on the entire range in one action.

[judgeh59]

sweet....glad someone was able to get it going faster....

[judgeh59]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.