Deleting rows with #N/A

[Guest]

I am using Excel 97 and want to delete rows of data which have a value of
#N/A in them which is the result from a vlookup formula.

I am using the following code but it is debugging with Type mismatch!

For i = 2 To LastRow

Cells(i, 5).Select

If Cells(i, 5).Value = "#N/A" Then
ActiveCell.EntireRow.Select
Selection.EntireRow.Delete
End If
Next i

Can anyone help with a solution, please?

 

[Nick Hodge]

Mark

Why not autofilter on that and delete the visible rows.
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Guest]

Okay,

But the rows with this value in will change, how do you prevent the wrong
rows being deleted?

Mark

 

[DMoney]

consider modifying your vlookup first I suggest using the
if iserror function in the lookup.
example below

=IF(ISERROR(VLOOKUP(#REF!,CSR,2,FALSE))=TRUE,0,VLOOKUP
(#REF!,CSR,2,FALSE))

in the event of a error ie.. #Name..#Ref, the lookup can
put in a value like xxxxx. From here it would be easy to
identify and remove all of the undesired rows via code you
have already written.

Regards,

 

[Tom Ogilvy]

Sub DeleteRows()
columns(5).SpecialCells(xlformulas,xlerrors).EntireRow.Delete
End Sub

 

[Dave Peterson]

I like Tom's suggestion best, but ...

If Cells(i, 5).Text = "#N/A" Then
or
if iserror(cells(i,5).value) then