Loop Macro gets progressively slower

marimar02

Board Regular
Joined
May 21, 2010
Messages
104
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:

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,889
Office Version
2013
Platform
Windows
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
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,889
Office Version
2013
Platform
Windows
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
 

marimar02

Board Regular
Joined
May 21, 2010
Messages
104
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
 

marimar02

Board Regular
Joined
May 21, 2010
Messages
104
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
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,889
Office Version
2013
Platform
Windows
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:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,889
Office Version
2013
Platform
Windows
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
 

marimar02

Board Regular
Joined
May 21, 2010
Messages
104
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
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,889
Office Version
2013
Platform
Windows
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 !
 

marimar02

Board Regular
Joined
May 21, 2010
Messages
104
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 !
 

Watch MrExcel Video

Forum statistics

Threads
1,099,900
Messages
5,471,379
Members
406,759
Latest member
jackflint

This Week's Hot Topics

Top