Loop Macro gets progressively slower

[marimar02]

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.

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:

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:

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

 

[Michael M]

Does it do the same on another PC ??
Can you upload the workbook to dropbox and post the link back here ?

 

[marimar02]

Good point. I have not tried a different PC. The spreadsheet is proprietary so I cannot share unfortunately.

Does it do the same on another PC ??
Can you upload the workbook to dropbox and post the link back here ?

 

[Michael M]

You could put dummy data in it ??

 

[marimar02]

I'd have to considerably change the spreadsheet and formulas because it's too revealing and some proprietary. I will go with your prior recommendation of using a filter instead. That's instantaneous vs. running a loop. I will try to build a shareable sheet workbook if I find time. I appreciate the offer to assist, really.

You could put dummy data in it ??

 

[Michael M]

DId you try it on another PC ?
With what your code does, I wouldn't think it's a PC or memory issue !!

 

[Michael M]

Another minor change to the pagebreak section.....but I don't think it will change anything, just cleaner

ActiveSheet.ResetAllPageBreaks
    With ActiveWindow.SelectedSheets.HPageBreaks
        .Add Before:=Rows("56:56")
        .Add Before:=Rows("211:211")
        .Add Before:=Rows("428:428")
        .Add Before:=Rows("493:493")
    End With