Is there a faster way

[Jim May]

NewLRow below = 7170
As a result this Macro runs for over 25 minutes;
Is there a faster way of getting this done?


Sub Macro3()
Application.ScreenUpdating = False
Dim NewLRow As Long
Dim r As Long
NewLRow = Cells(Rows.Count, 9).End(xlUp).Row
For r = NewLRow To 2 Step -1
If Cells(r, 9) = "" Then
Rows(r).Delete
End If
Next
Range("A1").Select
Selection.EntireRow.Delete
Selection.EntireColumn.Delete
Application.ScreenUpdating = True
End Sub

 

[Tom Ogilvy]

If the cells in the column are blank

Application.Calculation = xlManual
Columns(9).SpecialCells(xlBlanks).entireRow.Delete
Application.Calculation = xlAutomatic

 

[Jim May]

Thanks Tom;
Would restricting the range down from
Columns(9) [all rows 1-65000+] to say Range(I1:I7071)
shorten the time even more? like:

Range(I1:I7071).SpecialCells(xlBlanks).entireRow.Delete ?

Thanks,
JMay

 

[Tom Ogilvy]

Probably not. In this context, specialcells should not select beyond the
UsedRange. If your UsedRange extends well beyond the last filled cell, then
you might use what you suggest or use it any way: in any event it certainly
shouldn't make it slower I wouldn't think.

You can test it.

Sub AAAC()
Dim rng As Range
Set rng = Columns(9).SpecialCells(xlBlanks)
Set rng = rng.Areas(rng.Areas.Count)
Application.Goto rng(rng.Count)

End Sub

to see what the last cell addressed is.

--
Regards,
Tom Ogilvy

Thanks Tom;
Would restricting the range down from
Columns(9) [all rows 1-65000+] to say Range(I1:I7071)
shorten the time even more? like:

Range(I1:I7071).SpecialCells(xlBlanks).entireRow.Delete ?

Thanks,
JMay