How to delete multiple rows without a loop in Excel VBA

Question

Frequently we are asked how to delete rows based on criteria in one or more columns, and can we use a SpecialCells trick for this?

Answer 1

First, let me say categorically that there is nothing wrong with loops - they certainly have their place!

Recently we were presented with the below situation:

400000  |  Smith, John| 2.4   | 5.66|   =C1+D1
400001  |  Q, Suzy    | 4.6   | 5.47|   =C2+D2
400002  |  Schmoe, Joe| 3.8   | 0.14|   =C3+D3
Blank   |             |       |     |   #VALUE
Blank   |             |       |     |   #VALUE

The OP wanted to delete rows where Column A is blank, but there is a value in Column E.

I suggest that this is an example where we could make use of SpecialCells and a temporary Error Column to identify the rows to be deleted.

Consider that you might add a column H to try and identify those rows; in that row you could use a formula like below:

=IF(AND(A:A="",E:E<>""),"DELETE THIS ROW","LEAVE THIS ROW")

now, it is possible get that formula to put an error in the rows where I test returns True. The reason we would do this is a feature of Excel called SpecialCells.

In Excel select any empty cell, and in the formula bar type

=NA()

Next, hit F5 or CTRL+G (Go to... on the Edit menu) then click the Special button to show the SpecialCells dialog.

In that dialog, click the radio next to 'Formulas' and underneath, clear the checkboxes so that only Errors is selected. Now click OK

Excel should have selected all the cells in the worksheet with an Error (#N/A) in them.

The code below takes advantage of this trick by creating a formula in column H that will put an #N/A in all the rows you want to delete, then calling SpecialCells to find the rows, and clear (delete) them...

    Sub clearCells()
    '
    Dim sFormula As String
    '
    ' this formula put's an error if the test returns true, 
    ' so we can use SpecialCells function to highlight the
    ' rows to be deleted!

Create a formula that will return #NA when the formula returns TRUE

sFormula = "=IF(AND(A:A="""",E:E<>""""),NA(),"""")"

Put that formula in Column H, to find the rows that are to be deleted...

Range("H5:H" & Range("E65536").End(xlUp).Row).Formula = sFormula

Now use SpecialCells to highlight the rows to be deleted:

Range("H5:H" & Range("E65536").End(xlUp).Row).SpecialCells(xlCellTypeFormulas, xlErrors).entirerow.select

This line of code would highlight just Column A by using OFFSET in case instead of deleting the entire row, you wanted to put some text in, or clear it

Range("H5:H" & Range("E65536").End(xlUp).Row).SpecialCells(xlCellTypeFormulas, xlErrors).Offset(0, -7).select

and the below line of code will delete thhe entire row because we can :)

Range("H5:H" & Range("E65536").End(xlUp).Row).SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete shift:=xlup

' clean up the formula
Range("H5:H" & Range("E65536").End(xlUp).Row).Clear
'
End Sub

BTW, it's also possible WITH A LOOP if you really want one :)

One more thing, before Excel 2010 there was a limit of 8192 rows (I think because this feature went all the way back to 8-bit versions of Excel maybe)

The VBA legend Ron de Bruin (on whose website I first picked up this technique, among others) has something to say about this

Philip