Remove rows based on list of words

**jewseboy** · 04-24-2013, 04:31 AM

Hi Everyone

I have attached a simple test spreadsheet that contains two worksheets.

The first "domainnames" contains a list of domain names. I have included a sample set of 35 rows but there will be maybe 1000 or more.

The second "spamwords" is a list of words that I want to use to remove all rows if any of the words in the "spamwords" list are contained within the "domainnames" worksheet.

So as an example if the word "shoes" in "spamwords" worksheet is present in any of the rows in the worksheet "domainnames I would like the row to be deleted.

In this this example it would remove the rows soccersshoes-usa.com hothotshoes.com & running-shoesby.com from the "domainnames" worksheet

Hope that makes sense

thanks for your time to read this post

**martindwilson** · 04-24-2013, 06:11 AM

use advanced filter
see attached

advanced filter.
ignore any "could not determine range" messages if they pop up"
select filter list in place

select col a

list range should show domainnames!$A:$A
click in criteria range
select b1:b7
click ok
note the top of the criteria range has the same name as the top of the range to be filtered
also a formula has been used to append wildcard * to each end of the names from spamwords
once filtered you can simply select all the rows and delete

**jewseboy** · 04-24-2013, 09:00 AM

Awesome! It worked. Thanks for your work on this it really is appreciated

To put the cherry on the top as I have to run this several times daily I was trying to put it in a macro I have encountered two problems

1. Determining the entire list range and criteria range of both the domainnames and spamnames lists. These both change constantly on each run.

2. I want to keep a separate external spreadsheet with solely a list of spamnames that I could reference using your technique?

great work !