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?

--
Mark

Mark

Why not autofilter on that and delete the visible rows.
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


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

--
Mark

Okay,

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

Mark

"Nick Hodge" wrote:

Mark

Why not autofilter on that and delete the visible rows.
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


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

--
Mark

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,VLO OKUP
(#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,



-----Original Message-----
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?

--
Mark
.

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

--
Regards,
Tom Ogilvy


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

--
Mark

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

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

Mark wrote:

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?

--
Mark

--

Dave Peterson