Delete #N/A rows Formula

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

STEVEB

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
 
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
 
Don't you like the answer I give you in your other thread Steve???

Sorry Steve

You add some things I see now after reading Tom's answer
Please stay in you old thread next time
 
Back
Top