Loop Macro gets progressively slower

marimar02

Board Regular
Joined
May 21, 2010
Messages
128
Office Version
  1. 365
Platform
  1. Windows
Hello,

I'm running a loop macro to hide (or unhide) all rows in a range based on criteria in a cell. When I first open the spreadsheet and run "hide" or "unhide" macro (identical except for "yes" or "no" result), it goes fast, but if I try running it again, it slows down significantly. I don't have any filters or conditional formats that would slow it down perhaps.

Code:
Sub Hide_Detail()

    Dim Areas As Range
    Dim Cell As Range


    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .DisplayAlerts = False
        .ErrorCheckingOptions.InconsistentTableFormula = False
    End With


    On Error GoTo ErrorHandler


    Set Areas = Sheets("Settings").Range("V1:V10")


    For Each Cell In Areas


        Sheets(Cell.Value).Activate


    Call Calculate_HideShow_Condition
    Call Hide_Rows


    Application.GoTo Reference:=Range("L1"), Scroll:=True


    ActiveSheet.ResetAllPageBreaks
    Rows("56:56").Select
    ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
    Rows("211:211").Select
    ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
    Rows("428:428").Select
    ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
    Rows("493:493").Select
    ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
        
    Next Cell


ErrorHandler:


    Sheets("Settings").Activate


Set Areas = Nothing


    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
        .DisplayAlerts = True
    End With


ThisWorkbook.Save
            
End Sub


  • Call Calculate_HideShow_Condition:

Code:
Sub Calculate_HideShow_Condition()

Dim i As Integer
Dim rng As Range


    For i = 4 To 753


    Set rng = Range("K" & i)
    
        rng.FormulaR1C1 = _
        "simple formula here to return a Yes or No"
    
    rng.Value = rng.Value
    
    Next i


Set rng = Nothing


End Sub


  • Call Hide_Rows:

Code:
Sub Hide_Rows()     
    Dim i As Integer

With Activesheet

        For i = 4 To 753
            Select Case .Range("K" & i).Value
            Case "Yes"
                .Rows(i & ":" & i).EntireRow.Hidden = True
            End Select                    
        Next i
End With

End Sub
 
Last edited:

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Try changing this
Code:
Sub Hide_Rows()     
Dim i As long
application.screenupdating=False
        For i = 753 to 4 step -1
            If Range("K" & i).Value= "Yes" then Rows(i).EntireRow.Hidden = True
        Next i
application.screenupdating=true
End Sub
 
Upvote 0
And this one

Code:
Sub Calculate_HideShow_Condition()
application.screenupdating=False
With Range("K4:K753")
    .Formula = "simple formula here to return a Yes or No"
    .Value = .Value
End With
application.screenupdating=true
End Sub
 
Upvote 0
Curious if I need
Code:
A[COLOR=#333333]pplication.ScreenUpdating=False[/COLOR]
or "True" since it resides in the loop code. Also, what's the reason for backwards calculation? It covers the same amount of rows. Just trying to understand.

anyways, this didn't work. I appreciate the thought.

Try changing this
Code:
Sub Hide_Rows()     
Dim i As long
application.screenupdating=False
        For i = 753 to 4 step -1
            If Range("K" & i).Value= "Yes" then Rows(i).EntireRow.Hidden = True
        Next i
application.screenupdating=true
End Sub
 
Upvote 0
Would this work since my formula is RC Excel one.

here is the full formula:
Code:
Sub Calculate_HideShow_Condition()

Dim i As Integer
Dim rng As Range


    For i = 4 To 753


    Set rng = Range("K" & i)
    
        rng.FormulaR1C1 = _
        "=IF(RC[5]=""CURRENT MONTH"",""No"",IF(OR(RIGHT(RC[9],6)=""Direct"",RIGHT(RC[9],7)=""Indirect"",RIGHT(RC[9],8)=""Other"",RC[9]=""Month"",RC[9]=""Period"",RC[9]=""Hours""),""Yes"",IF(SUM(RC[10]:RC[17])<>0,""No"",IF(OR(AND(RC[9]<>"""",RC[10]=""""),AND(R[1]C[9]<>"""",R[1]C[10]="""")),""No"",IF(OR(LEFT(R[-1]C[9],5)=""TOTAL"",LEFT(R[-1]C[9],8)=""SUBTOTAL"",RC[9]=""D" & _
        "escription""),""No"",""Yes"")))))" & _
        ""
    
    rng.Value = rng.Value
    
    Next i


Set rng = Nothing


End Sub

And this one

Code:
Sub Calculate_HideShow_Condition()
application.screenupdating=False
With Range("K4:K753")
    .Formula = "simple formula here to return a Yes or No"
    .Value = .Value
End With
application.screenupdating=true
End Sub
 
Upvote 0
Backwards running for the row hiding, means it is only hiding the rows from the bottom, not ALL of the rows each time

This should be much faster
Code:
Sub Calculate_HideShow_Condition1()
Application.ScreenUpdating = False
With Range("K4:K753")
    .Formula = "=IF(P4=""CURRENT MONTH"",""No"",IF(OR(RIGHT(T4,6)=""Direct"",RIGHT(T4,8)=""Indirect"",RIGHT(T4,8)=""Other"",T4=""Month"",T4=""Period"",T4=""Hours""),""Yes"",IF(SUM(U4:AB4)<>0,""No"",IF(OR(AND(T4<>"""",U4=""""),AND(T5<>"""",U5="""")),""No"",IF(OR(LEFT(T3,5)=""TOTAL"",LEFT(T3,8)=""SUBTOTAL"",T4=""Description""),""No"",""Yes"")))))"
    .Value = .Value
End With
Application.ScreenUpdating = True
End Sub
 
Last edited:
Upvote 0
This would also be quicker hiding rows

Code:
Sub MM()
With Range("K4").CurrentRegion
    .AutoFilter Field:=1, Criteria1:="<>Yes", Operator:=xlAnd, Criteria2:="<>"
End With
End Sub
 
Upvote 0
Hmmmmm. Same. 1st run, it takes 2 seconds. 2nd run, it takes about 30 seconds. 3rd run, it takes longer than a minute. I wonder if it has to do with memory (though my PC has Core i5 vPro 8th Gen with 16Gb RAM). I'm curious if Excel is running out of memory?

Backwards running for the row hiding, means it is only hiding the rows from the bottom, not ALL of the rows each time

This should be much faster
Code:
Sub Calculate_HideShow_Condition1()
Application.ScreenUpdating = False
With Range("K4:K753")
    .Formula = "=IF(P4=""CURRENT MONTH"",""No"",IF(OR(RIGHT(T4,6)=""Direct"",RIGHT(T4,8)=""Indirect"",RIGHT(T4,8)=""Other"",T4=""Month"",T4=""Period"",T4=""Hours""),""Yes"",IF(SUM(U4:AB4)<>0,""No"",IF(OR(AND(T4<>"""",U4=""""),AND(T5<>"""",U5="""")),""No"",IF(OR(LEFT(T3,5)=""TOTAL"",LEFT(T3,8)=""SUBTOTAL"",T4=""Description""),""No"",""Yes"")))))"
    .Value = .Value
End With
Application.ScreenUpdating = True
End Sub
 
Upvote 0
So when this is run on the sheets in question, are there other codes / formulas / sheets that may be affected by the results ?
The codes themselves seem pretty straightforward, and should run almost instantly !
 
Upvote 0
I tried to look but nothing is affected by these results. Formula turns a cell into a "yes" or a "no" and there aren't any dependents attached to it. It's only used within the Hide or Show code.

I'm curious if the Excel is running low on memory with every run (PC is powerful, brand new). I tried to "Unload" all the variables but that doesn't help. It's literally a progressive slowdown. I ran it 3 times and 3rd time it takes minutes per sheet. First run almost instantaneous, 2nd run about a half a minute, 3 run close to 5 min per sheet.

So when this is run on the sheets in question, are there other codes / formulas / sheets that may be affected by the results ?
The codes themselves seem pretty straightforward, and should run almost instantly !
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top