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


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.


This question generated 41 answers. To proceed to the answers, click here.

This thread is current as of July 09, 2014.


For more resources for Microsoft Excel