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 '@'](http://stackoverflow.com/a/16901714/138938) — Jon Crowell, Jun 28 '13 at 21:15

Andy G · Answer 1 · 2013-06-28T21:57:54.780

3

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 :)

edited Jun 28 '13 at 21:57

answered Jun 28 '13 at 21:27

Andy G

19,232
5
47
69

+1 Good answer if the cell type is actually error based. As it may also be formula based consider `Range("B:B").SpecialCells(xlFormulas, 16).EntireRow.Delete` – brettdj Jun 29 '13 at 12:19
@brettdj Thank you. That should be `xlCellTypeFormulas` in your statement - although it doesn't matter too much as `xlFormulas` has the same value ;). Just to clarify, the 16 in Brett's statement identifies formulas that result in errors. – Andy G Jun 30 '13 at 00:24

score 0 · Answer 2 · answered Jun 28 '13 at 20:41

0

Work from bottom-up, checking the values in column B for N/A, if found, you can use:

ActiveSheet.Cells(currentRow,1).EntireRow.Delete

answered Jun 28 '13 at 20:41

Joseph

5,070
1
25
26

Thanks for the reply. How can I use this to create a conditional statement to check if the column in the row contains `N/A`? – AnchovyLegend Jun 28 '13 at 21:00
@AnchovyLegend do you have any experience with VBA? Just trying to gauge my answer for you – Joseph Jun 28 '13 at 21:01
Also, where am I suppose to insert this code? I apologize, I am very new to excel and no I do not have any experience with VBA... – AnchovyLegend Jun 28 '13 at 21:02
@AnchovyLegend ahhh ok. here is a great resource to get you started: http://www.anthony-vba.kefra.com/vba/vbabasic1.htm – Joseph Jun 28 '13 at 21:04
Looping is unnecessary for this question. – brettdj Jun 29 '13 at 12:20
@brettdj true. Always multiple ways to do these things. – Joseph Jun 29 '13 at 15:18

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

2 Answers2