Delete #N/A rows
 

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



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Delete #N/A rows
 

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(xlCellTypeFo rmulas, xlErrors).Select
Selection.EntireRow.Delete
On Error GoTo 0
End Sub




--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"STEVEB" wrote in message ...
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



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Delete #N/A rows
 

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" wrote in message
...
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(xlCellTypeFo rmulas, xlErrors).Select
Selection.EntireRow.Delete
On Error GoTo 0
End Sub




--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"STEVEB" wrote in message
...
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



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from
http://www.ExcelForum.com/