CODEWITHSUNDEEP
excelvbaoptimization
Gaussian Blur
Gaussian Blur

Reputation: 638

VBA - Code Execution is Extremely Slow

I've got a fairly simple bit of code I'm using to loop through an excel sheet of data. Basically, I have over 150,000 rows of data and I want to delete every row except ones that have "Instrument Failure" in them, in which case I want that row and the one immediately above and below it (which may contain details relating to the failure). Here's my code:

Sub UnsignedFailure()
Application.ScreenUpdating = False
'Start point
Set r = Range("E1")

'Loop, total rows is a function, executes quickly
For t = 3 To TotalRows
    'Check for a failure
    k = InStr(1, r.Cells(t, 1).Value, "Instrument Failure")
    'Verify that it corresponds to a GC
    b = InStr(1, r.Cells(t, 1).Offset(0, -3).Value, "GC")
    If k <> 0 And b <> 0 Then
    'Skip two rows if it is true
        t = t + 2
    Else
    'Delete the previous row since this row is not a failure
        r.Cells(t - 1, 1).EntireRow.Delete
    'Go back a row to account for the deleted row
        t = t - 1
    End If
Next t

Application.ScreenUpdating = True
End Sub

I've checked this by stepping through and it works. However, even having the code break every time the "IF" is true, the time between breaks seems very long (maybe 5 min to check 20,000 cells). If I let the code run without breaking, I have yet to get it to complete (computer freezes). Am I missing something or being inefficient in my code? Any help would be greatly appreciated!

Upvotes: 3

Views: 17127

Answers (1)

Pete Garafano
Pete Garafano

Reputation: 4913

My experience with excel macros has taught me it is much more efficient to copy data matching your conditions to a new sheet, not deleting rows that don't match.

Every time you delete a row, it takes excel a few milliseconds to recalculate the sheet (even if there are no formulas, it still checks every cell to see if it needs updating) and update the display to the user. You already disable screen updating, which will definitely cut some of this time off. Another way is to turn off Excel Calculation, put Application.Calculation = xlCalculationManual at the top of your sub to disable recalculation and Application.Calculation = xlCalculationAutomatic at the bottom to enable it again. This site has some good pointers on optimization of VBA code for excel.

As I said, I think the most efficient thing would be to move data to a new sheet when it matches your conditions.

Upvotes: 12

Related Questions