Delete All Rows That Column A value is not in Column A of Sheet2
 

I have a large dataset that I'm pulling out of an Access database using a
query to extract the records I want (around 1000 records).
May second data set is a large Excel table that has around 10,000 rows.

I'd like to be able to delete all rows in the Excel table (call it
sht_Excel) that do not have a value in Column A that is listed in the list
from the query (call it sht_DB) .
What is the most efficient way to do this?

I came up with the foloowing approaches:

1) Sort both lists by Column A. Start with sht_DB.Range("A1").
If sht_Excel.Range("A1") doesn't equal it, delete the entire row.
If sht_Excel.Range("A1") equals it, check sht_Excel.Range("B1")
If sht_Excel.Range("B1") doesn't equal it, compare to sht_DB.Range("B1").

Continure in this fashion marching down through both lists.

2) Adding a Vlloukup function to the sht_Excel list, and delete the row if
it doesn;t returna match

There has to be a better way todo this than brute force.
Is it better to link the Excel Table to a dummy Acces database, and do a
query in it?
Any help would be appreciated.

Delete All Rows That Column A value is not in Column A of Sheet2
 

Seems like you are spending more time asking than actually doing. It takes
less than a minute to do the second. Record a macro while you do it manually
and then generalize the macro.

if you are deleting the rows individually based on the error, then you have
missed the point

select the column with the formula/errors and do

Edit=goto =Special, select formulas and errors, then do Edit=Delete and
select entire row.

--
Regards,
Tom Ogilvy


wrote in message
...

I have a large dataset that I'm pulling out of an Access database using a
query to extract the records I want (around 1000 records).
May second data set is a large Excel table that has around 10,000 rows.

I'd like to be able to delete all rows in the Excel table (call it
sht_Excel) that do not have a value in Column A that is listed in the list
from the query (call it sht_DB) .
What is the most efficient way to do this?

I came up with the foloowing approaches:

1) Sort both lists by Column A. Start with sht_DB.Range("A1").
If sht_Excel.Range("A1") doesn't equal it, delete the entire row.
If sht_Excel.Range("A1") equals it, check sht_Excel.Range("B1")
If sht_Excel.Range("B1") doesn't equal it, compare to
sht_DB.Range("B1").

Continure in this fashion marching down through both lists.

2) Adding a Vlloukup function to the sht_Excel list, and delete the row if
it doesn;t returna match

There has to be a better way todo this than brute force.
Is it better to link the Excel Table to a dummy Acces database, and do a
query in it?
Any help would be appreciated.

Delete All Rows That Column A value is not in Column A of Sheet2
 

Thanks for your help.

I would have gone line by line looking for the error and deleting the
entire row selectively.