VBA code needs to run multiple times to work correctly

[anichols]

Hello all,

I have this seemingly simple code that will delete rows that have blanks in a particular column, but I doesn't delete all of them unless I run it 5 times or more. Any ideas out there as to what would cause this?

Sub deletestuff()
Dim R As Long
    For R = 2 To 100
        If Range("B" & R).Value = "" Then Rows(R).Delete
    Next R
End Sub

 

[steve the fish]

Yes you need to loop backwards. If you delete row 10, for example, then row 11 becomes row 10 and your loop will miss it. Replace the For line with:

For R = 100 to 2 Step -1

 

[Fluff]

Another option (if column B does not have formulae) is

Sub anichols()
   Range("B2", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlBlanks).EntireRow.Delete
End Sub

 

[anichols]

Works great! Thanks for both solutions

 

[Fluff]

Glad we could help & thanks for the feedback.