Delete rows if any cell in Sheet 1 rows matches any cell in Sheet2 column

**newbie88** · 08-21-2012, 07:18 PM

Hi!

I'm needing an Excel 2003 VBA macro that would perform the following:

If any cell in Sheet1!A2:D2 matches any cell in Sheet2!B5:B7, then row Sheet1!A2:D2 is deleted;
If any cell in Sheet1!A3:D3 matches any cell in Sheet2!B5:B7, then row Sheet1!A3:D3 is deleted;
If any cell in Sheet1!A4:D4 matches any cell in Sheet2!B5:B7, then row Sheet1!A4:D4 is deleted;

...and so on, through...

If any cell in Sheet1!A9:D9 matches any cell in Sheet2!B5:B7, then row Sheet1!A9:D9 is deleted;

The remaining/resulting rows would be:

Col1	Col2	Col3	Col4
pizza	red	tim	pork
yellow	green	shrimp	cheese
dan	purple	tara	white
fish	bread	brown	heather
pink	mary	ryan	teresa

Thank you.

**JBeaucaire** · 08-21-2012, 08:07 PM

F1 = "Key"
F2 = =IF(OR(ISNUMBER(SEARCH(Sheet2!$B$5:$B$7, A2&B2&C2&D2))),"yes", "no")

Confirm the array formula above by pressing CTRL-SHIFT-ENTER to activate the array, you will see curly braces { } appear around your formula.

Copy F2 down.

Turn on the Data > Filter > AutoFilter on column F

Filter for "yes"

Delete all the visible rows.