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.

 

[lekh0602]

Thanks a lot but it seems to still crash - do you think it is the amount of data that causes the problem?

 

[vds1]

Try these solutions..

Repairing corrupted files in Excel - Excel

Excel: How to Troubleshoot Crashing and Not Responding Issues with Excel

 

[jasonb75]

Why try to filter / loop?

Sub Button3_Click()
    Columns("Z:AB").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
End Sub

 

[Rick Rothstein]

Why try to filter / loop?

Sub Button3_Click()
    Columns("Z:AB").SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
End Sub

I agree with your suggestion, but just want to make a couple of comments. 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. Hence, my suggestion for your main code line would be to write it this way...

Columns("Z:AB").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete

 

[jasonb75]

Thanks Rick, I was having brainfreeze on the correct syntax so just grabbed the specialcells bit from the macro recorder.

 

[Rick Rothstein]

Thanks Rick, I was having brainfreeze on the correct syntax so just grabbed the specialcells bit from the macro recorder.

The macro recorder will give you the full, spelled-out main argument namewith the "CellType" part in it, but if you are typing the code out longhand, it is more convenient to omit that part of the text, hence my suggestion. Given that xlCellType@@@@ and xl@@@@ (where @@@@ is a stand-in for the various argument name types) are both defined the same, I don't understand why the longer version is even shown in the help files at all... I don't think including that "extra" text clarifies anything over and above the argument name which omits it.

 

[lekh0602]

Hi Jason and Rick,

Thanks for your inputs. I just tried using Rick's suggestion and wrote the code like this:

Sub CMRDatenDSc_Button3_Click() 'Used for identifying cells with #N/A and then deleting the relevant rows


Columns("Z:AB").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete


End Sub

However, I get a "Run-time error 1004 No cells were found". As I am new to VBA I can't figure out what I'm doing wrong. Would any of you know?

Thanks.

 

[andrewkard]

"No cells were found", it means that excel can't find cells with #N/A

 

[Rick Rothstein]

I just tried using Rick's suggestion and wrote the code like this:

Sub CMRDatenDSc_Button3_Click() 'Used for identifying cells with #N/A and then deleting the relevant rows

How is the #N/A getting into your cells... as the result of a formula or are you typing it in?

 

[lekh0602]

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.