Speed Up Macro Segment

[katekoz]

Hello,

I have a very long macro that's taking a REALLY long time to run (so long that my computer goes into standby mode while it runs). I narrowed the problem area of the code down to the segment below, and am wondering if anyone has any tips on how to make this more efficient. It does what it's supposed to do, it's just too slow to even use. The "Overall User Data" sheet has just over 10k lines and about 800 lines will be deleted by this code.

yr = ThisWorkbook.Sheets("New Data Add").Range("A2").Value
mth = ThisWorkbook.Sheets("New Data Add").Range("B2").Value

'Delete rows from the previous year of the month of data that was just added.

For r = ThisWorkbook.Sheets("Overall User Data").UsedRange.Rows.Count To 1 Step -1
    If ThisWorkbook.Sheets("Overall User Data").Cells(r, "A") = yr - 1 Then
        If ThisWorkbook.Sheets("Overall User Data").Cells(r, "B") = mth Then
            ThisWorkbook.Sheets("Overall User Data").Rows(r).EntireRow.Delete
        End If
    End If
Next

I think I read somewhere that you can select all the lines you want to delete, then delete the selection at the end and that the act of selecting rather than deleting would help macros run faster. I'm not sure if this would work for entire rows, or if it's even a valid idea.

Any advice anyone can give would be much appreciated!

Kate

 

[ISY]

Hi

Use

yr = ThisWorkbook.Sheets("New Data Add").Range("A2").Value
mth = ThisWorkbook.Sheets("New Data Add").Range("B2").Value

    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
        .EnableCancelKey = xlErrorHandler
    End With
    ActiveSheet.DisplayPageBreaks = False

'Delete rows from the previous year of the month of data that was just added.

For r = ThisWorkbook.Sheets("Overall User Data").UsedRange.Rows.Count To 1 Step -1
    If ThisWorkbook.Sheets("Overall User Data").Cells(r, "A") = yr - 1 Then
        If ThisWorkbook.Sheets("Overall User Data").Cells(r, "B") = mth Then
            ThisWorkbook.Sheets("Overall User Data").Rows(r).EntireRow.Delete
        End If
    End If
Next
    
    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayAlerts = True
        .StatusBar = False
        .EnableCancelKey = xlInterrupt
    End With
Next

 

[katekoz]

Much better! It still takes about 5 mins to run, but that's more manageable. I did have my screen updating turned off, but adding those other settings seems to have helped.

I'm wondering, should I add those settings to the very beginning and end of the entire macro rather than just the segment? Are there any negative consequences that can occur with any of these settings that I should watch out for if I do choose to add them?