delete entire row if multiple criteria are satisfied

[4evra]

I've got this code:

Please Login or Register  to view this content.

It works flawlessly.
However,the code doesnt deletes entire row if the criteria is meet either in column B or columng G.
I though i could handle the data im filtering with current code,but its overwhelming.

So,if anyone would be kind enough to modify the code above into a code that requires both B and G columns to meet the criteria from "sheet2" list in order to delete a row.

Right now,if column B contains word i listed and column G doesnt,row is still kept.And its giving me more data than i can handle,so i need to cut it down :D

Thanks in advance

[JOHN H. DAVIS]

Do you mean like this?

Please Login or Register  to view this content.

[4evra]

Hmm,i inserted it into data,it deleted all of it.Or when it didnt delete it all,it left only 1st entry undeleted
And i made sure that there is some data that shouldn't get deleted and vice versa

Here's the example file,i beleive it's easier for u to see it for youself than to try to make sense of my talk

Book1.xlsm


Now,from all those entries,a fine working code wouldn't delete rows 5-19 since both of their columns B and G contain the words listed in A column in sheet 2

[JOHN H. DAVIS]

In the sample workbook, there is a blank Column F. Is it supposed to be there? If so Column G will not contain any of the words on Sheet2 Column A. Rows 5-19 does contain words listed on Sheet2. Please restate your criterias.

[4evra]

In the sample workbook, there is a blank Column F. Is it supposed to be there? If so Column G will not contain any of the words on Sheet2 Column A. Rows 5-19 does contain words listed on Sheet2. Please restate your criterias.

Yes.F and D columns are empty,web query leaves them empty as they contain images on site from which im extracting data.

The code provided in my first post works on the same layout,when F and D are empty columns.
Now,difference is,original code works like this:
column B contains word from sheet2(columnA) = i keep that row where there is a match
Column G contains word from sheet2(columnA) = i keep that row where there is a match
So,if there is a match at column B OR G,the row is kept.

I want it to be AND instead of OR.
So,the row will be kept only if there is match in BOTH columns B AND G in that row.Otherwise,its gonna get deleted,no matter if it contains a match in either B or G,as long as it doesnt contain match at both columns.


Let me know if it is any clearer
Greets

[JOHN H. DAVIS]

OK, I think I get it now. I misunderstood before.

[mike7952]

maybe this will work, I could be wrong but you have said column G but i think your meaning column H

Please Login or Register  to view this content.

[JOHN H. DAVIS]

BTW I think you want to compare Columns B and H to Sheet2 since on you're sample sheet they are the only Columns which contain words from Sheet2. Please correct me if I'm wrong.

[JOHN H. DAVIS]

FWIW:

Please Login or Register  to view this content.

[4evra]

maybe this will work, I could be wrong but you have said column G but i think your meaning column H

Indeed.my apologies,got it misstyped.Column H is the right one.

BTW I think you want to compare Columns B and H to Sheet2 since on you're sample sheet they are the only Columns which contain words from Sheet2. Please correct me if I'm wrong.

Yes,the sheet 2 contains words i want code to compare with words in columns B and H.

Both of your answers gave me an working code,so i'm gonna mark the thread as solved and add reputation to both of you,since u took the time to help me.

However,as i 'newbie' in VBA code,i can grasp the idea behind John H.Davis's code,i see he used a vlookup function.
I also see he made this:

Please Login or Register  to view this content.

Like i said,i'm not very good at VBA,but from the looks of it,the "criteria" is limited to cells A1:A5 in sheet2?
So,in case i expand a list of words on that sheet2,i would need to modify that range into $A$1:$A$100 for example?

Mike's code looked mindbending to me,i couldn't recognise anything in it.
Then i noticed the

Please Login or Register  to view this content.

which i missed somehow on first look and it made sense.

Once again,many thanks to both of you
Cheers

[JOHN H. DAVIS]

You're welcome. Glad to hear you got a working solution.