How to detect #N/A using VBA

[hh]

Hi all,

I am using VBA to construct a spreadsheet for a user to analyse data. The user pastes data exported from two host systems, and I build formulae to harmonise the data (date formats, etc.). In one sheet I create vlookup formulae between the two sheets, and there are always a lot of #N/As. I would like to give the user the option to delete these rows, but I can't seem to trap these.

Some sample code:

Sub FormatSheet()

Application.ScreenUpdating = False

Dim rng As Range, rng2 As Range
Dim lastrow As Long

Set rng = Range("D65536").End(xlUp)
Do Until rng.Row = 5
Set rng2 = rng.Offset(-1)
If rng.Offset(0, 7) = "#N/A" Then rng.EntireRow.Delete
Set rng = rng2
Loop

End sub

I've tried all sorts of weird constructs using Iserror, but all to no avail.



As always, any help will be greatly appreciated,

hh

 

[jimboy]

This works for me;

If WorksheetFunction.IsNA(rng.Offset(0, 7)) Then rng.EntireRow.Delete

 

[TommyGun]

Tried to adapt this to your needs. Test it and see if it works.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> FormatSheet()
    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range

    <SPAN style="color:#00007F">Set</SPAN> rng = Range(Range("K6"), Range("D65536").End(xlUp).Offset(0, 7))
    rng.SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete
    <SPAN style="color:#00007F">Set</SPAN> rng = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

 

[hh]

Guys,

thanks for your help with this - your solutions worked a treat.

hh