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...

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...

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...

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

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...

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...

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...

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...

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...

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...