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

  • Thread starter Thread starter Shirley Munro
  • Start date Start date
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
 
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
 
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
 
Back
Top