Deleting blank rows faster than my macro

[mayday1]

The code below deletes rows if column B is empty. It works fine but does about 1 or 2 rows per second and it sometimes has to go through a couple thousand rows. I never know how many rows will be in my file - sometimes 1000, sometimes 4000. I set the limit at 4500 but it's possible there could be more. Is there a faster way?

Dim lRow As Long
Dim iCntr As Long
lRow = 4500
For iCntr = lRow To 1 Step -1
If Cells(iCntr, 2).Value = "" Then
Rows(iCntr).Delete
Application.StatusBar = iCntr
End If
Next

 

[Twollaston]

The code below deletes rows if column B is empty. It works fine but does about 1 or 2 rows per second and it sometimes has to go through a couple thousand rows. I never know how many rows will be in my file - sometimes 1000, sometimes 4000. I set the limit at 4500 but it's possible there could be more. Is there a faster way?

Dim lRow As Long
Dim iCntr As Long
lRow = 4500
For iCntr = lRow To 1 Step -1
If Cells(iCntr, 2).Value = "" Then
Rows(iCntr).Delete
Application.StatusBar = iCntr
End If
Next

You can try this

Sub DeleteBlankB()
'


Columns("B:B").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
End Sub

 

[mayday1]

Well that was instantaneous so, yeah - I guess that's better.
Thanks

 

[Twollaston]

Well that was instantaneous so, yeah - I guess that's better.
Thanks

haha nice, happy to help

 

[Fluff]

You could also simplify that code like

Columns(2).SpecialCells(xlBlanks).EntireRow.Delete

as it's normally better to avoid selecting things.

 

[Twollaston]

You could also simplify that code like

Columns(2).SpecialCells(xlBlanks).EntireRow.Delete

as it's normally better to avoid selecting things.

I always do everything from recording cause I don't know the language well.

This is a nice tip, I always prefer less code if possible. Thanks for adding this

 

[Fluff]

You're welcome.