Process a row if a string search finds certain words from a list
 

Hello!
I have a list of real estate ads (text) in a column (column B). It has over
300 rows. I want to delete every row that has certain pre-defined words in
the ad text; eg. realtor, property, management, etc. The list of words I
would like to search for is a variable list that I may add to over time. So
I'm thinking of having a separate sheet ("'exclusions") that has all the
words i want to look for in its column A.

So I would like to have a macro that does the following (below is in pseudo
code):

for each word in "exclusions"(a:a) do
for i= 1 to lastrow do
if cell B(i) contains word then
delete row
end if
end for

Am I making sense? Again, the ad text is of variable length, and i'm
looking to find certain words based on a list located in another sheet that
may change.

Thanks very much in advance for any/all help! :)

Process a row if a string search finds certain words from a list
 

Reuel Wrote:
Hello!
I have a list of real estate ads (text) in a column (column B). It ha
over
300 rows. I want to delete every row that has certain pre-define
words in
the ad text; eg. realtor, property, management, etc. The list of word
I
would like to search for is a variable list that I may add to ove
time. So
I'm thinking of having a separate sheet ("'exclusions") that has al
the
words i want to look for in its column A.

So I would like to have a macro that does the following (below is i
pseudo
code):

for each word in "exclusions"(a:a) do
for i= 1 to lastrow do
if cell B(i) contains word then
delete row
end if
end for

Am I making sense? Again, the ad text is of variable length, and i'm
looking to find certain words based on a list located in another shee
that
may change.

Thanks very much in advance for any/all help! :)
Hi
sounds like (not tested)

Code
-------------------

sub test()
dim r as range, txt() as string,i as long, ii as long, LastR as long
with sheets("exclusions")
for each r in .range("a1",.range("a" & rows.count).end(xlup))
if not isempty(r) then
i=i+1:redim preserve txt(1 to i)
txt(i)=r.value
end if
next
end with
with activesheet
LastR=.range("b" & rows.count).end(xlup).row
for i=lastr to 1
for ii=1 to ubound(txt)
if instr(.cells(i,2),txt(ii))0 then
.rows(i).entirerow.delete
exit for
next
next
end with
erase txt
end sub

-------------------

--
jindo
-----------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php...fo&userid=1313
View this thread: http://www.excelforum.com/showthread.php?threadid=49609