Delete a row if cell contains text

Question

I have used this code to delete all rows which are blank:

Sub DelBlankRows()
    Columns("L:L").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

Now I need to delete all rows that are not blank. Like if a cell in Column L:L contains any text or number then it deletes the entire row.

gtwebb gtwebb · Accepted Answer · 2014-02-12T22:06:08

One way to do it is to get rid of all cells that have constants or formulas which should cover all cells with stuff in them.

Gordon

Sub DelNonBlankRows()
    Columns("L:L").SpecialCells(xlCellTypeConstants).EntireRow.Delete
    Columns("L:L").SpecialCells(xlCellTypeFormulas).EntireRow.Delete
End Sub

Delete a row if cell contains text

1 Answers