Hi Tom...

The #N/A that I need deleting was being created by an error response to a
vlookup table. By using iserror in my formula the #n/a is now a 1. I think
deleting #n/a's was too problematic. Is it easier to delete rows where a 1
occours in column b, rather than #n/a's?

Thanks

Gordon

"Tom Ogilvy" wrote:

Sub DeleteNA()
With Activesheet
On Error Resume Next
.columns(2).SpecialCells(xlFormulas,xlErrors).Enti reRow.Delete
On Error goto 0
End With
End Sub

--
Regards,
Tom Ogilvy



wrote in message
ups.com...
I ran it on some test data and it worked fine. I will take a look at
it again. Do you have any other code that may be firing as a result of
changes in the worksheet?


Gordon wrote:
Hi...

Thought about it for 30 seconds, the screen flickered, but then it did
nothing...

Any clues?

Cheers

Gordon...

" wrote:

Gordon,

Try this. It checks for the N/A error.

Alan

Sub Delete_NA()
Dim MyCell As Range
For Each MyCell In Range("B1:B3000")
If Application.WorksheetFunction.IsNA(MyCell) = True Then
MyCell.EntireRow.Delete
End If
Next
End Sub


Gordon wrote:
Hi...

The procedure stops on this line: Run-time error 13 Type mismatch

If MyCell.Value = "#N/A" Then

Any clues?

Thanks in advance...

Gordon...



" wrote:

In the Worksheet module:


Sub Delete_NA()
Dim MyCell As Range
For Each MyCell In Range("B1:B3000")
If MyCell.Value = "N/A#" Then
MyCell.EntireRow.Delete
End If
Next
End Sub

Gordon wrote:
Hi...

I have 3000 records. In column B there are sporadic #N/A values.
I need some
nicely packaged code that will delete all rows where a #N/A
appears in B, so
that there are no gaps in the rows. Sounds easy but I'm
struggling.

Any help would be most appreciated.

Thanks

Gordon...