How to search across 2 columns for keywords from a list and copy each row to a new sheet?

Hello Everyone,

I've been able to find great advices numerous times before in this forum, but this is the first time I couldn't find an answer to a new problem. My apologies if this has been answered before but hopefully someone can point me in the right direction.


I have 3 worksheets:
1. Sheet1 is a huge Data Set with values starting from column A10 to I10. A10 to I10 is the header.
2. Sheet2 is blank with headers on row 1.
3. Sheet3 is a list of keywords under column A.

My goal is create a macro that will look at each of the keywords listed in Sheet3 and search for them in Column B and Column D of Sheet1. The keywords may appear in either Column B or Column D in Sheet1. For each row it finds, I need it to copy the entire row to Sheet2 starting on row 2 since row 1 has headers.

What I'm able to get so far is just searching under one column only instead of searching across two columns.

I hope my explanation isn't too confusing and I thank you in advance. I've attached a sample of the file with mock data and posted the VBA I've been using so far to search under one column.

Any help?

DataSample.xlsx

Please Login or Register  to view this content.

Try this it works on the sample but will need minor amendments for the full package but it's simple enough.

Please Login or Register  to view this content.

As an option

Please Login or Register  to view this content.

Holger,
I am not sure what are you referring to? Could you please explain?

Mike,
Thanks!
I thought, generally speaking in computer programming, 1 equals to True and 0 is false

Most electronic systems work on negative logic where the inverse is true :-)....... There is a good reason for it but I can't for the life of me remember why...

Andy!
Thanks,
So you reckon 1 is false and o is true

Not in Excel no....

So, my first assumption was right then

Yeah, sorry... Didn't mean to confuse the situation

Andy,
I am still not sure why do I need to use -1, as suggested by Mike and Holger,

No idea mate...

I've tried all the macros and they worked really well. I am so appreciate of everyone's help, thank you so much!

AB33, is there a way to add in a line somewhere that will work like a "ClearContents" where the search result will be cleared away each time the Macro is used as the Data Set on Sheet1 will always be updated with more rows of data and some Keywords in Sheet3 may be removed.

Thank you again everyone.

Keith,
Amended code. Clears the data in sheet2-output sheet before you run the code.

Please Login or Register  to view this content.

This is awesome, thanks AB33!

You have no idea how much this will help. Thanks everyone!

Keith,
You are welcome!