Why does my macro run so slow???

[Rags]

Hi

Why does this macro run so slow? It takes about 2 min 40 secs to run through a sheet of 170 columns by 2400 rows.

Sub Delete_Deleted_Schemes()

    Application.ScreenUpdating = False

    Dim LastRow As Long, c As Long
    LastRow = Range("A65536").End(xlUp).Row
    
    For c = LastRow To 2 Step -1
    If Cells(c, "C") <> "FY06/07 Programmed" Then
    Rows(c).Delete
    End If
    Next c
    
End Sub

Or is this normal?

 

[Jonmo1]

are there alot of formulas on the sheet?

deleting a row will cause the sheet to calculate...therefor calculating every time a row is deleted....

you can try turning off the auto calculate at the beginnging of macro..

Application.Calculation = xlManual

then turn it back on at the end of macro

Application.Calculation = xlAutomatic

 

[Von Pookie]

Could also write the macro to filter the data instead of looping.

For example,
- Filter to display data that does not contain "FY06/07 Programmed" in column C
- Delete visible rows
- Turn off filter.

 

[TinaP]

You are also scanning through 65536 rows instead of just the 2400 that are used.

 

[Jonmo1]

You are also scanning through 65536 rows instead of just the 2400 that are used.

LastRow = Range("A65536").End(xlUp).Row

this line only happens once in the code as it is outside the loop...and it doesn't take long (a split second) to do that code, so it's not a contributer of the 2 minutes 40 seconds described. This is common code for determining the last used row.

 

[TinaP]

Thanks, jonmo1. I couldn't think of the exact code.

 

[Rags]

are there alot of formulas on the sheet?

deleting a row will cause the sheet to calculate...therefor calculating every time a row is deleted....

you can try turning off the auto calculate at the beginnging of macro..

Application.Calculation = xlManual

then turn it back on at the end of macro

Application.Calculation = xlAutomatic

There are 4 columns of formulas. I turned off auto calculate and got the time down to 55 seconds.

Could also write the macro to filter the data instead of looping.

For example,
- Filter to display data that does not contain "FY06/07 Programmed" in column C
- Delete visible rows
- Turn off filter.

This is what I've done in the past and is probably the direction to head.

Thanks