Delete rows if specific columns contain cells with "N/A"

[lekh0602]

Hi,

I would like to set up a button where cells with "N/A" in specific columns will then delete the relevant row - the columns are Z, AA and AB.

When I use the code below it does not seem to work.

The code is the following:

Sub Button3_Click()
'Declare variables
Dim rngFound As Range 'Used for the find loop
Dim rngDel As Range 'Used to store matching rows
Dim strFirst As String 'Used to store the first cell address of the find loop to prevent infinite loop


'Search for #N/A in column C
Set rngFound = Columns("Z").Find("#N/A", Cells(Rows.Count, "Z"), xlValues, xlWhole)


'Make sure something was found
If Not rngFound Is Nothing Then
'Found something, record first cell address
strFirst = rngFound.Address


'Start loop
Do
'Check if cells in column Z, AA, and AB are all #N/A
If Cells(rngFound.Row, "Z").Text = "#N/A" _
And Cells(rngFound.Row, "AA").Text = "#N/A" _
And Cells(rngFound.Row, "AB").Text = "#N/A" Then


'Found they are all #N/A, store the row in rngDel
If rngDel Is Nothing Then Set rngDel = rngFound Else Set rngDel = Union(rngDel, rngFound)


End If


'Advance the loop to the next cell with #N/A in column Z
Set rngFound = Columns("Z").Find("#N/A", rngFound, xlValues, xlWhole)


'Exit loop when back to the first cell
Loop While rngFound.Address <> strFirst
End If


'If rngDel has anything in it, delete all of its rows
If Not rngDel Is Nothing Then rngDel.EntireRow.Delete


'Object variable cleanup
Set rngFound = Nothing
Set rngDel = Nothing


End Sub

I would appreciate any inputs to what I am doing wrong.

Thanks.

 

[Rick Rothstein]

Yes, the #N/A is the result of formulas from column Y to AE - not all data necessary for the different formulas is available from another sheet. That's why I want to delete the rows where the cells show #N/A.

Sorry, I missed the switch in columns from the original Z:AB to Y:AE. See if this code works for you...

Sub Button3_Click()
  On Error GoTo NoNAs
  Columns("Y:AE").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
NoNAs:
End Sub

 

[jasonb75]

Rick

Just looking at your latest suggestion, I was given a couple of useful pointers by a very helpful MVP on this forum, thought you might find them interesting

The "CellType" part of every argument name for SpecialCells can be omitted and the function will still work. So, for your suggested code, xlFormulas will work the same as xlCellTypeFormulas. Second, I think it makes the code clearer and more self-documenting if you use built-in variables rather than hard-coded "magic" numbers.... instead of 16, I would use the built-in constant xlErrors.

Sub Button3_Click()
  On Error GoTo NoNAs
  Columns("Y:AE").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
NoNAs:
End Sub

 

[lekh0602]

Hi Rick,

The code doesn't seem to work since the #N/A stay in the cells. But I get no error message when running the code.

 

[Rick Rothstein]

Hi Rick,

The code doesn't seem to work since the #N/A stay in the cells. But I get no error message when running the code.

What version of Excel are you using and approximately how rows of data are there?

 

[jasonb75]

Is the button on the same sheet as the #N/A! errors?

 

[lekh0602]

Hi Rick and Jason,

I'm using Excel 2007 but have access to Excel 2010 if necessary. The number of rows will differ from maybe 2,000 rows to almost 40,000 rows...

Jason, the button is on the same worksheet as the #N/A errors.

 

[Rick Rothstein]

I'm using Excel 2007 but have access to Excel 2010 if necessary. The number of rows will differ from maybe 2,000 rows to almost 40,000 rows...

Do me a favor and try the same file that failed using Excel 2010 instead of Excel 2007 and see if it works for you using it... and, of course, let us know as well. There is a limitation on the SpecialCells function in versions of Excel prior to 2010 that you may be exceeding.

 

[lekh0602]

Hi Rick,

Suddenly the file cannot run macros though I have enabled macros - I don't know what happened. One moment it worked and then suddenly not. I will try your suggesten when the file is (hopefully) working again.

 

[jasonb75]

Could you post one of the formula that returns n/a in your sheet.

I could be on the wrong track, but given that in an earlier post, the error returned by the code was resulting from no errors found in the sheet, I thought it might be good to eliminate the possibility.

=ISNA("#N/A") Returns false, so the code will not find it
=ISNA(#N/A) Returns true
=ISNA(NA()) Returns true, this also matches the error that you would get from a mismatch with a lookup formula, which the code should find.

 

[lekh0602]

Hi Jason,

This is one of the formulas (column Z): =VLOOKUP(S6;Wagendetails!F$2:G$310;2;FALSE)