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

S

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
 
G

gearoi

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
 
D

Debra Dalgleish

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
 
Top