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.

 

[jfarc]

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

Even a blind squirrel finds a nut now and then.....

Cheers!