Compare two tables and Delete where there isnt a match (VBA)

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
2,332
Office Version
  1. 365
Platform
  1. Windows
I have two tables on two different tabs. I want to make a copy of one of the tables and then cycle through it and delete any rows where there isnt a match to the first table

Tab Name SQD: copy the table named tblSQD to a new tab and name the tab "Search_Results"

Then I want to delete any rows in the new table if the field [ItemID] does not match items in the table named TableSQDSearch field name [Part_Number_Required] which is on a tab named "Search"

Any help is appreciated - Thank You!
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Hi, did you consider using Power Query?
  1. Select table TableSQDSearch and on the Data ribbon select From Table/Range and close and load as connection only
  2. Select table tblSQD and repeat the step From Table/Range and stay inside the PQ UI
  3. Then select Merge Queries
  4. Select in the bottom half of the screen the table TableSQDSearch
  5. Click the field ITEMID and the field Part_Number_Required to make the join
  6. In the join type select LEFT ANTI (rows only in first)
  7. Load this query in the sheet named Search_Results
 
Upvote 0
Thanks GraH, but I was trying to avoid Power Query - reason being is that the people using this report are all working remotely through VPN. Running this query via VPN is extremely slow. It takes anywhere from 10-15 minutes to run this simple query. That is why I though that maybe I could use VBA to loop through one of the tables and delete where there wasn't a Match. The VPN my company uses is extremely slow when it comes to Power Query where one of the data sources is an Microsoft Access table.
 
Upvote 0
Hi @gheyman, I see, I understand.

How fast/slow is loading TableSQDSearch (assuming this is your Access Table)?
If that's within reasonable timing, you might want to use Table.Buffer(TableSQDSearch) in the second query, before merging. Then tweak the code to reference this line. Something like...
Power Query:
StepBefore Merge= some code...
, bTableSQDSearch=Buffer(TableSQDSearch)
,Merge =Table.NestedJoin(StepBeforeMerge, {"ItemID"}, bTableSQDSearch, {"Part_Number_Required"}, "TableSQDSearch", JoinKind.LeftAnti)

See if that helps to limit run time.

Can't help with VBA :(
 
Upvote 0
Thanks! I will give it a try and let you know what happens.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top