Using a Macro to delete all rows containing #N/A

[Shirley Munro]

Hi

I am recording a Macro which uses a VLookup to fill in values in column A from another file. Where a match is not found it displays #N/A. I can use AutoFilter to filter out all the #N/A's and then delete these rows. The problem is that the orginal file is downloaded on a regular basis and the number of rows varies and therefore the number of #N/A's. Is there macro code which will automatically delete all rows containing #N/A's.

Thanks

Shirley

[gearoi]

Is this a multiple post?

I'm sure I just answered this...

Basically you need to use code and a loop too.

[Norman Jones]

Hi Shirley,

Assume tha the #N/A values are in column A, then try:

'===================>>
Public Sub TestA()
On Error Resume Next 'In case no error values found!
Columns("A").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
On Error GoTo 0
End Sub
'<<===================

Change A to suit.

---
Regards,
Norman



"Shirley Munro" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi
>
> I am recording a Macro which uses a VLookup to fill in values in column
> A from another file. Where a match is not found it displays #N/A. I
> can use AutoFilter to filter out all the #N/A's and then delete these
> rows. The problem is that the orginal file is downloaded on a regular
> basis and the number of rows varies and therefore the number of #N/A's.
> Is there macro code which will automatically delete all rows containing
> #N/A's.
>
> Thanks
>
> Shirley
>
>
> --
> Shirley Munro
> ------------------------------------------------------------------------
> Shirley Munro's Profile:
> http://www.excelforum.com/member.php...nfo&userid=836
> View this thread: http://www.excelforum.com/showthread...hreadid=401910
>

[Jim May]

Sample (Change to suit): (Looks for #N/A in Column B)

Sub DeleteNAs()
lrow = Cells(Rows.Count, "B").End(xlUp).Row
For i = lrow To 1 Step -1
If IsError(Cells(i, 2).Value) Then
Cells(i, 2).EntireRow.Delete
End If
Next i
End Sub


"Shirley Munro" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi
>
> I am recording a Macro which uses a VLookup to fill in values in column
> A from another file. Where a match is not found it displays #N/A. I
> can use AutoFilter to filter out all the #N/A's and then delete these
> rows. The problem is that the orginal file is downloaded on a regular
> basis and the number of rows varies and therefore the number of #N/A's.
> Is there macro code which will automatically delete all rows containing
> #N/A's.
>
> Thanks
>
> Shirley
>
>
> --
> Shirley Munro
> ------------------------------------------------------------------------
> Shirley Munro's Profile:
> http://www.excelforum.com/member.php...nfo&userid=836
> View this thread: http://www.excelforum.com/showthread...hreadid=401910
>

[gearoi]

Hi Shirley,

Assume tha the #N/A values are in column A, then try:

'===================>>
Public Sub TestA()
On Error Resume Next 'In case no error values found!
Columns("A").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
On Error GoTo 0
End Sub
'<<===================

Change A to suit.

---
Regards,
Norman



"Shirley Munro" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi
>
> I am recording a Macro which uses a VLookup to fill in values in column
> A from another file. Where a match is not found it displays #N/A. I
> can use AutoFilter to filter out all the #N/A's and then delete these
> rows. The problem is that the orginal file is downloaded on a regular
> basis and the number of rows varies and therefore the number of #N/A's.
> Is there macro code which will automatically delete all rows containing
> #N/A's.
>
> Thanks
>
> Shirley
>
>
> --
> Shirley Munro
> ------------------------------------------------------------------------
> Shirley Munro's Profile:
> http://www.excelforum.com/member.php...nfo&userid=836
> View this thread: http://www.excelforum.com/showthread...hreadid=401910
>

Nice work - specialcells! Though quite specific and hard to generalise