Delete all Rows containing #N/A once a VLookup has been performed

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?action=getinfo&userid=836
View this thread: http://www.excelforum.com/showthread.php?threadid=401909

Debra
Mon Sep 05 08:21:51 CDT 2005

If there are no other types of errors in column A, you can record a
macro as you do the following steps:

Select column A
Choose Edit>Go To
Click the Special button
Select Formulas, and clear all the check marks except Errors
Click OK
Choose Edit>Delete
Select Entire Row, click OK

Shirley Munro wrote:
> 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
>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

gearoi
Mon Sep 05 08:13:39 CDT 2005


Use a loop -

dim down as long

down = 0
do while range(top of your range).offset(down,0).value <>""

'add the delete the particular row code here
down = down + 1
loop

that way you don't need to faff about with autofilters and if you tur
off calculation in the macro too this should be pretty quick

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