Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete all rows where Column A contains Text | Excel Worksheet Functions | |||
Is there a quick way to delete all duplicate rows in a column? | Excel Discussion (Misc queries) | |||
macro to find something in column A and delete 5 rows below it | Excel Programming | |||
Delete rows with text and blanks in column A | Excel Programming | |||
delete rows if dublicate cell value in column 2 | Excel Programming |