Delete blank rows other than first column

Question

I have written a macro to delete the row if it is a blank row or if in column B the cell contains the string XYZ. However, this macro can take a couple minutes to run if there is 200+ rows of data. Can anyone provide anything more efficient in VBA format?

Sub DeleteBlanks()

Dim lr As Long, r As Long
For r = Cells(Rows.Count, "B").End(xlUp).Row To 1 Step -1
    Range("B" & r).Replace "*XYZ*", "", xlWhole
    If Range("B" & r).Value = "" Then
        Range("B" & r & ":Q" & r).Delete (xlShiftUp)
    End If
Next r

Application.ScreenUpdating = False

End Sub

Answer 1

First of all, the screen updating should be disabled before the proccess, and re-enabled after that, so the screen will not flash, and load of resources will not be high.

Other than that, text replacement is completely unneeded in your case.

By reading your current code, I assume you consider a blank row if it's empty on column B.

Try this:

Sub DeleteBlanks()

Application.ScreenUpdating = False
Dim lr As Long, r As Long
For r = Cells(Rows.Count, "B").End(xlUp).Row To 1 Step -1
    If Range("B" & r).Value = "" Or Range("B" & r).Value Like "*XYZ*" Then
        Range("B" & r & ":Q" & r).Delete (xlShiftUp)
    End If
Next r
Application.ScreenUpdating = True


End Sub