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]

Rick, the code still doesn't seem to work - no reaction when I push the button.

 

[jasonb75]

Ok, so it is a genuine formula error which the code should find. Are there definitely errors in the sheet, or have they already been deleted while testing some of the earlier suggestions?

Rick, noting your comment about the limit on the specialcells function pre 2010, that was something I was not aware of so I decided to look into it a bit more, it would appear that the limit doesn't cause an error, just incorrect function of the code.

After reading an article by Ron de Bruin, SpecialCells limit problem I decided to do a little testing.

In short, it would appear that when you exceed the limit of 8192 areas the executed without the specialcells argument, so

Columns("Y:AE").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete

Would, in effect be executed as

Columns("Y:AE").EntireRow.Delete

Without any error message or warning of any kind being presented to the user.

 

[Rick Rothstein]

Rick, the code still doesn't seem to work - no reaction when I push the button.

In that case, I think you are going to have to post a copy of your workbook to one of the free file sharing websites on the web. If you don't have a means of posting files, you can use this one...

http://www.box.net/files

 

[lekh0602]

Jason, there are still #N/A data in some of the cells in the column(s).

Rick, I will see if I can upload a copy - thanks.

Have a good weekend, both of you

 

[lekh0602]

Hi Rick,

Unfortunately, I cannot share the data in my spreadsheet - then I would have to build up a trial version.

But I just tried to use your first suggestion again:

Columns("Y:AE").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete


However, now I get the Run-time Error '1004': Cannot use that command on overlapping selections.

Thanks.

 

[andrewkard]

Unfortunately, I cannot share the data in my spreadsheet - then I would have to build up a trial version.

Can you change it and post the data? We need the data.

 

[jasonb75]

Can you change it and post the data? We need the data.

That's not strictly true, we just need to know what is causing the code to fail.

Run-time Error '1004': Cannot use that command on overlapping selections.

A little research suggests that the error message quoted could be caused by something like hidden columns or merged cells.

Is there anything like that in your workbook?

Also is there any event code in the workbook?

 

[jfarc]

Hi Rick,

Unfortunately, I cannot share the data in my spreadsheet - then I would have to build up a trial version.

But I just tried to use your first suggestion again:

Columns("Y:AE").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete


However, now I get the Run-time Error '1004': Cannot use that command on overlapping selections.

Thanks.

Try this in place of the "Y:AE" version above:

On Error Resume Next
Columns("Y").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
Columns("Z").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
Columns("AA").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
Columns("AB").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
Columns("AC").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
Columns("AD").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
Columns("AE").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete

It will at least get rid of your 'Cannot use that command on overlapping selections' error.

 

[jfarc]

Sorry, just to add a little explanation to post #38 above.

You are getting the 'Cannot use that command on overlapping selections' error due to the case of having an '#N/A' in multiple columns on the same row. The command first identifies all cells with the '#N/A' condition. Then, it sees the first occurrence in the first column it will delete the row, but then when it tries to delete the same row again when it finds it in the next column over, it errors out because it doesn't exist anymore. (my guess on this)

There may be a more efficient way than the code suggestion I had, but I got your same error on the "Y:AE" version, but it worked fine with this way since it breaks up the row deletion process into separate segments.

 

[jasonb75]

You are getting the 'Cannot use that command on overlapping selections' error due to the case of having an '#N/A' in multiple columns on the same row. The command first identifies all cells with the '#N/A' condition. Then, it sees the first occurrence in the first column it will delete the row, but then when it tries to delete the same row again when it finds it in the next column over, it errors out because it doesn't exist anymore. (my guess on this)

Guess or not, you have certainly identified the cause of the error.

This is my best effort at code that works.

Set Rng = Columns("Y:AE").SpecialCells(xlFormulas, xlErrors).EntireRow
Union(Rng, Rng).Delete