Loop Macro gets progressively slower
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Loop Macro gets progressively slower
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2010
    Location
    Laguna Niguel, CA
    Posts
    94
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Loop Macro gets progressively slower

    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 by marimar02; Aug 13th, 2019 at 07:00 PM.

  2. #2
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,503
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Loop Macro gets progressively slower

    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
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  3. #3
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,503
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Loop Macro gets progressively slower

    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
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  4. #4
    Board Regular
    Join Date
    May 2010
    Location
    Laguna Niguel, CA
    Posts
    94
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loop Macro gets progressively slower

    Curious if I need
    Code:
    Application.ScreenUpdating=False
    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.

    Quote Originally Posted by Michael M View Post
    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

  5. #5
    Board Regular
    Join Date
    May 2010
    Location
    Laguna Niguel, CA
    Posts
    94
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loop Macro gets progressively slower

    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
    Quote Originally Posted by Michael M View Post
    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

  6. #6
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,503
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Loop Macro gets progressively slower

    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 by Michael M; Aug 13th, 2019 at 08:48 PM.
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  7. #7
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,503
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Loop Macro gets progressively slower

    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
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  8. #8
    Board Regular
    Join Date
    May 2010
    Location
    Laguna Niguel, CA
    Posts
    94
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loop Macro gets progressively slower

    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?

    Quote Originally Posted by Michael M View Post
    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

  9. #9
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,503
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Loop Macro gets progressively slower

    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 !
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  10. #10
    Board Regular
    Join Date
    May 2010
    Location
    Laguna Niguel, CA
    Posts
    94
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loop Macro gets progressively slower

    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.

    Quote Originally Posted by Michael M View Post
    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 !

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •