This question already has an answer here:
I have a spreadsheet that can import a bunch of data from 3 different types of files. It formats it so that the output will be the same for all three, in one case it needs to delete rows based on what is in the cells of that row. The problem is that this takes about 1.5 to 2 minutes to do compared to seconds it takes for the formatting of the other files (the ones without rung deletes). The worksheet starts out around 4000 rows and deletes about 1600 of them. My current way of doing it works fine and everything works but is there a way of doing this that would run faster?
rw = 1
Lastrow = 2600
NewRow = 0
NxtChk2:
If (Worksheets("ARCSRT.CND").Cells(rw, "c") >= "0") And (Worksheets("ARCSRT.CND").Cells(rw, "d") = "") Then
Worksheets("ARCSRT.CND").Rows(rw).EntireRow.Delete
rw = rw - 1
End If
If (Worksheets("ARCSRT.CND").Cells(rw, "b") = "0.0") And (Worksheets("ARCSRT.CND").Cells(rw, "c") = "") Then
Worksheets("ARCSRT.CND").Rows(rw).EntireRow.Delete
rw = rw - 1
End If
If rw = Lastrow Then GoTo LATER
rw = rw + 1
GoTo NxtChk2
If sorting of the list is an option, instead of deleting row one by one just mark them with a "flag", i.e. letter "X" in a separate column, once the cycle finished, get your code to sort data on that column and just delete the whole batch of flagged rows in one go, it will do the deletion operation once and not 1600 times.
following from my comment, try the following
this to go before the loop:
Dim rng As Range
Set rng = Nothing
in the loop instead of row delete do this:
If rng = Nothing Then
rng = Cells(rw, "A")
Else
rng = Union(rng, Cells(rw, "A"))
End If
and then after the loop has finished:
rng.EntireRow.Delete
bear in mind i am not sure how will it run on 1600 non-consecutive rows, run on a backup copy