If any cells in column B contains string X, delete row and shift cells up

Question

I am not very experiences in excel, and not sure if this possible, but I am working with a very large excel spreadsheet and some of the cells in column B include values such as N/A.

I was wondering if there is a way to create some conditional statement that will delete all rows, if the cell in column B of that row includes N/A, and shift cells up after deleting?

I truly appreciate any advice as this will save me a great deal of time.

Many thanks in advance!

Don't use a loop. Use an autofilter. Use this as your guide: Efficient way to delete entire row if cell doesn't contain '@' — Jon Crowell

Andy G Andy G · Accepted Answer · 2013-06-28T21:27:27

IF these are #N/A (system) errors, then using GoTo Special (Constants, Errors) makes this straight-forward:

Sub DeleteErrorRows()
    On Error Resume Next    'in case there are no errors in cells
    Range("B:B").SpecialCells(xlCellTypeConstants, 16).EntireRow.Delete
    On Error GoTo 0     'resume error handling
End Sub

I suspect it may be possible to use Replace to replace 'N/A' with '#N/A' then use my procedure, although I haven't tested this. Added Yes, it should work:

Sub DeleteErrorRows()
    Range("B:B").Replace "N/A", "#N/A"
    On Error Resume Next    'in case there are no errors in cells
    Range("B:B").SpecialCells(xlCellTypeConstants, 16).EntireRow.Delete
    On Error GoTo 0     'resume error handling
End Sub

Actually, I think this could be more generally applied, replacing some cell values with '#N/A' as a mechanism for deleting rows :)

If any cells in column B contains string X, delete row and shift cells up

2 Answers