Delete #N/A rows

  • Thread starter Thread starter STEVEB
  • Start date Start date
S

STEVEB

How do you automatically delete rows that are returned as "#N/A" after
performing a VLOOKUP. Below is my MACRO that performs a VLOOKUP. Any
suggestions to expand to have the #N/A rows deleted while running this
MACRO would be appreciated.




Dim rng As Range
With Worksheets("Group 40")
Set rng = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
rng.Offset(0, 1).Formula = _
"=VLOOKUP(A2," & _
"'[Reportable Accounts.xls]Sheet1'!" & _
"$A$2:$B$1147,1,FALSE)"

rng.Offset(0, 1).Formula = rng.Offset(0, 1).Value

rng.Offset(0, 2).Formula = _
"=VLOOKUP(A2," & _
"'[Reportable Accounts.xls]Sheet1'!" & _
"$A$2:$B$1147,2,FALSE)"

rng.Offset(0, 2).Formula = rng.Offset(0, 2).Value

Range("A1").Select
Sheets("Macro").Select
Range("A1").Select

End Sub


Thanks,

Steve
 
You can use specialcells
This will work for all errors in column A (not only N/A) , don't know if you want that

Sub test()
On Error Resume Next
ActiveSheet.Columns("A").SpecialCells(xlCellTypeFormulas, xlErrors).Select
Selection.EntireRow.Delete
On Error GoTo 0
End Sub
 
Sub test()
On Error Resume Next
ActiveSheet.Columns("A").SpecialCells(xlFormulas, xlErrors) _
.EntireRow.Delete
On Error GoTo 0
End Sub

Should work as Well.

--
Regards,
Tom Ogilvy


Ron de Bruin said:
You can use specialcells
This will work for all errors in column A (not only N/A) , don't know if you want that

Sub test()
On Error Resume Next
ActiveSheet.Columns("A").SpecialCells(xlCellTypeFormulas, xlErrors).Select
Selection.EntireRow.Delete
On Error GoTo 0
End Sub




--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)




How do you automatically delete rows that are returned as "#N/A" after
performing a VLOOKUP. Below is my MACRO that performs a VLOOKUP. Any
suggestions to expand to have the #N/A rows deleted while running this
MACRO would be appreciated.




Dim rng As Range
With Worksheets("Group 40")
Set rng = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
rng.Offset(0, 1).Formula = _
"=VLOOKUP(A2," & _
"'[Reportable Accounts.xls]Sheet1'!" & _
"$A$2:$B$1147,1,FALSE)"

rng.Offset(0, 1).Formula = rng.Offset(0, 1).Value

rng.Offset(0, 2).Formula = _
"=VLOOKUP(A2," & _
"'[Reportable Accounts.xls]Sheet1'!" & _
"$A$2:$B$1147,2,FALSE)"

rng.Offset(0, 2).Formula = rng.Offset(0, 2).Value

Range("A1").Select
Sheets("Macro").Select
Range("A1").Select

End Sub


Thanks,

Steve
Click to expand...
Click to expand...
 
Back
Top