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

Hi

I am recording a Macro which uses a VLookup to fill in values in colum
A from another file. Where a match is not found it displays #N/A.
can use AutoFilter to filter out all the #N/A's and then delete thes
rows. The problem is that the orginal file is downloaded on a regula
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 containin
#N/A's.

Thanks

Shirle

--
Shirley Munr
-----------------------------------------------------------------------
Shirley Munro's Profile: http://www.excelforum.com/member.php...info&userid=83
View this thread: http://www.excelforum.com/showthread.php?threadid=40191

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

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"
wrote in message
news:Shirley.Munro.1uw02h_1125925526.0297@excelfor um-nospam.com...

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

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

Is this a multiple post?

I'm sure I just answered this...

Basically you need to use code and a loop too

--
gearo
-----------------------------------------------------------------------
gearoi's Profile: http://www.excelforum.com/member.php...fo&userid=2657
View this thread: http://www.excelforum.com/showthread.php?threadid=40191

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

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"
wrote in message
news:Shirley.Munro.1uw02h_1125925526.0297@excelfor um-nospam.com...

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

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

Norman Jones Wrote:
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

wrote in message
news:Shirley.Munro.1uw02h_1125925526.0297@excelfor um-nospam.com...

Hi

I am recording a Macro which uses a VLookup to fill in values i
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 delet
these
rows. The problem is that the orginal file is downloaded on
regular
basis and the number of rows varies and therefore the number o
#N/A's.
Is there macro code which will automatically delete all row
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 generalis

--
gearo
-----------------------------------------------------------------------
gearoi's Profile: http://www.excelforum.com/member.php...fo&userid=2657
View this thread: http://www.excelforum.com/showthread.php?threadid=40191