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

As others mentioned, turn off screenupdating at the beginning. Also, if there are any formulas in the range, calculation mode to manual. — Ron Rosenfeld, Oct 15 '15 at 15:45

score 1 · Answer 1 · answered Oct 15 '15 at 15:21

I would add the ScreenUpdating line to the top, and also turn calculation to manual:

Sub DeleteBlanks()

Dim lr As Long, r As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

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 = True
Application.Calculation = xlCalculationAutomatic

End Sub

As you have it, the entire macro runs, then the screenUpdating is turned off. You can speed it up by putting that up front, then turning it back on when the macro is finished.

score 1 · Answer 2 · answered Oct 15 '15 at 15:26

1

In addition to what @BruceWayne said, I will shorten the code

 Range("B" & r).Replace "*XYZ*", "", xlWhole
    If Range("B" & r).Value = "" Then

With

If Range("B" & r).Value = "" Or InStr(1, Range("B" & r).Value, "XYZ") > 0 then

That will lower the actions that the code needs to make.

answered Oct 15 '15 at 15:26

Balinti

1,524
1
11
14

While your idea is indeed an improvement, it is not optimal. Since VBA does not short circuit evaluation both expressions on either side of the `Or` will always be evaluated, with your proposed enhancement. In this case the optimal refactoring is to use `Select Case True`. – Excel Hero Oct 15 '15 at 16:52

score 0 · Accepted Answer · answered Oct 15 '15 at 15:27

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

Excel Hero · Answer 4 · 2015-10-15T16:14:56.173

This solution should be virtually instantaneous:

Public Sub Colin_H()
    Dim v, rCrit As Range, rData As Range
    With [a1]
        Set rData = .Resize(.Item(.Parent.Rows.Count).End(xlUp).Row, .Item(, .Parent.Columns.Count).End(xlToLeft).Column)
    End With
    Set rCrit = rData.Resize(2, 2).Offset(, rData.Columns.Count + 1)
        rCrit.Resize(1) = rData(1, 2): rCrit(2, 1) = "*": rCrit(2, 2) = "<>*xyz*"
    rData.AdvancedFilter xlFilterCopy, rCrit, rCrit.Resize(1, 1).Offset(, 2)
    With rCrit.Resize(1, 1).Offset(, 2).Resize(rData.Rows.Count, rData.Columns.Count)
        v = .Value2
        rData = v
        .ClearContents
        rCrit.ClearContents
    End With
End Sub

Notice that there is no looping, no row shifting, and no iterated range construction.

This uses the advanced filter of the range object to filter your records in one quick blast to a range adjacent to your source data. The result is then copied over the source without using the clipboard. There is no quicker or more efficient way to achieve your objective.

@ColinHawthorne I'm here to help if you still need help on this. — Excel Hero, Oct 19 '15 at 18:20

Delete blank rows other than first column

4 Answers4