Search Loop VBA

tljenkin

Board Regular
Joined
Jun 14, 2007
Messages
147
Hello All,

I have a massive csv file thats linked to an excel file as "data model" & connection only, lets call it Master.

Now this csv file arrives weekly with a different name, so week 1, its called Week 1 and that rolls each week.

In the Master, you have the following tabs: Data and Reference tabs.

Data has the dump from the csv, about 100,000 lines. These lines are contained in a Table created automatically by Excel once the data query selection is retrieved. Query already preset in Master.
Reference has 5 columns each populated with items that must be deleted from Data

For three of those columns in Reference, the items contained will look for exact matches in a column in Data. Eg In cell A2 of Reference, you have the word "ball" and this has to be searched for in column A in Data. There are exact matches there. For every instance discovered, the VBA routine should delete the entire row.

For the remaining two columns, this is not an exact match. Using same example, ball would be searched for in cells than contain "soft ball" etc. VBA should search for an instance of ball in the text string in that column and delete entire row as well.

The Ask:
  1. When the Master is opened, an input box should pop up asking to select path and name of csv file. This should update the VBA code and the source in query settings.
  2. VBA that can Loop through all items in column A of Reference against column A of Data and delete rows of all exact matches
  3. VBA that can Loop through all items in column B of Reference against column B of Data and delete rows of all matches found within a text string
With the above, I should be able to edit the code for the other 3 columns in Reference.

Thanking you in advance Great VBA Gurus.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

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