Macro to delete a whole row based on a blank cell

[Ashe77]

Hi

I'm trying to create a macro whereby a whole row can be deleted if there are blank cells in column B

I have the below which works perfect in terms of deleting the rows BUT keeps on running after completing, endlessly going. I have to ESC to stop it. What's wrong?

Thanks in advance

Sub DeleteRow()
Dim rngFind As Range


Do
Set rngFind = Activesheet.Range("B1:B200").Find(What:="", LookAt:=xlWhole)
If Not rngFind Is Nothing Then rngFind.EntireRow.Delete
Loop Until rngFind Is Nothing


Set rngFind = Nothing
End Sub

 

[gallen]

Slightly different approach. When deleting rows its better to start at the bottom and work up:

Sub DeleteRow()
    Dim lLastRow As Long, l As Long
    lLastRow = Range("B" & Rows.Count).End(xlUp).Row
    
    'Start at bottom row and work up finishing at row 2(change as required)
    For l = lLastRow To 2 Step -1
        If Range("B" & l) = "" Then Rows(l).Delete
    Next
End Sub

 

[Ashe77]

Sir, you are a gent. Thank you so much.

 

[gallen]

Sir, you are a gent. Thank you so much.

You're welcome. I had a very similar issue a while back and got my answer here. When deleting rows it's always better to start at the last row with a value and work up.