Quickest way to delete rows

[y0gster]

I have basically hit a wall with this problem since switching to office 2007. In office 2003 the whole code ran 4 seconds.

This is the portion that slows it down, this is the code that came from office 03 and in 07 it took 30 seconds to run. (Basically the whole code is running this portion 3x since it will go through 3 sheets). So the total time would go to 1min 20 seconds. This is already with screenupdating to false and calculation to manual.

For i = 2 To Range("A65536").End(xlUp).Row
If Range("A" & i).Value <> "" And Range("H" & i).Value = "" Then
Rows(i & ":" & i).Delete Shift:=xlUp
i = i - 1
End If
Next i

I then decided to change the code to

Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

This would take 12 seconds each putting the total time to around 40 seconds

I know I already cut the time to 50% but is there any faster way to do this. TIA

 

[kpark91]

Hi, try

Application.ScreenUpdating = False
For i = Range("A" & Rows.count).End(xlUp).Row To 2 Step -1
    If Range("A" & i).Value <> vbNullString And Range("H" & i).Value = vbNullString Then
       Range("A" & i).EntireRow.Delete
    End If
 Next i
Application.ScreenUpdating = True

 

[y0gster]

Thanks kpark but the code actually got slower going on for 40 seconds each making the total 1 min and 30 seconds

 

[Armando Montes]

Autofilter is faster, look here:
http://www.rondebruin.nl/delete.htm