When you change the cells from a formula to a value, then this will not
work:

On Error Resume Next
ActiveSheet.Columns("B").SpecialCells(xlFormulas, xlErrors) _
EntireRow.Delete
On Error GoTo 0

You need to have it look for constants like this:

On Error Resume Next
ActiveSheet.Columns("B").SpecialCells(xlConstants, xlErrors) _
EntireRow.Delete
On Error GoTo 0

--
Regards,
Tom Ogilvy


"STEVEB" wrote in message
...
I tried to delete rows in a MACRO that are returned as #N/A after
performing a VLOOKUP (The VLOOKUP MACRO works well). The MACRO I have
to delete #N/A rows is not working & I was hoping someone has any
suggestions. In my VLOOKUP the data is pasted as values, by doing so
is the #N/A not recognized as an error? Should I combine the two
MACROS into one? My VLOOKUP code & Delete N/A rows code is as follows:

VLOOKUP

Worksheets("Group 40").Activate

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

End Sub

DELETE #N/A Rows

On Error Resume Next
ActiveSheet.Columns("B").SpecialCells(xlFormulas, xlErrors) _
EntireRow.Delete
On Error GoTo 0

End Sub

Thanks for your help



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