Code running really slowly

I'm running the following code on about 8,000 rows of data. The data has been pasted as values so there are no formulas being affected. This code deletes every row where the value in column E is zero.

When running this, Excel just hangs for a long time, till I finally just killed the program. I copied about 10 rows to another sheet within the workbook, and only 2 of those rows should have been deleted by this macro. I ran the macro again on my smaller test size sheet and it worked, but it took 20 seconds....

Is there any way to speed this up?

Note: I have a new I5 processor with 8GB of ram, I don't think this is an issue with computer power.

Sub Caseware()

Application.ScreenUpdating = False
Application.Calculation = xlManual
Dim i As Long

For i = Cells(Rows.Count, "a").End(xlUp).Row To 2 Step -1
If Cells(i, "e") = 0 Then Rows(i).Delete
Next i

End Sub

Originally Posted by Leith Ross

Hello The Phil,

I have an idea that might work. Are there any formulas in column "E"?

There are no formulas on that sheet at all. Though there are lots of other sheets with lots of complex formulas, the recalculate is set to manual so that shouldn't matter.

Originally Posted by romperstomper

It really shouldn't be that slow - do you have any event code in the sheet?

Probably quicker to only delete once:

Sub Caseware()
Dim rngtodelete as range
Application.ScreenUpdating = False
Application.Calculation = xlManual
Dim i As Long

For i = Cells(Rows.Count, "a").End(xlUp).Row To 2 Step -1
If Cells(i, "e") = 0 Then
If rngtodelete is nothing then
Set rngtodelete = cells(I, " e")
Else 
Set rngtodelete = union(rngtodelete, cells(I, "e"))
End if
Next i
If not rngtodelete is nothing then rngtodelete.entirerow.delete
End Sub

Hi,

I went to try this but got a compile error on the row "Next i". I'm not sure how to fix that.

Just thinking out loud here.
Would this work?

Sub Try()
    With Range("E1:E8000")    '<---- Change as required
        .Replace 0, "", xlWhole
        .SpecialCells(4).EntireRow.Delete
    End With
End Sub

Originally Posted by jolivanes

Just thinking out loud here.
Would this work?

Sub Try()
    With Range("E1:E8000")    '<---- Change as required
        .Replace 0, "", xlWhole
        .SpecialCells(4).EntireRow.Delete
    End With
End Sub

I tried this on a sample sheet with 16 rows, 4 of which should have been deleted. It worked but it took over 30 seconds to run.

It's looks like there's something wrong with the workbook itself. If I make a new workbook and run your script, then it works pretty much instantly....

What kind of formulae do you have in the workbook?
I had a similar problem years ago. Some functions recalculate whenever a change is made.
I changed the culprits and the problem was solved.
I personally don't know anything at all about these functions but if you post the formulae someone might be able to give you an alternative.

Originally Posted by romperstomper

Sorry - missed an end if:

I should have mentioned this, I tried adding an end if because I saw that too, but then I get the following error:

Run-time error '13':
Type mismatch