How do you delete a row when it contains a text string within other text
--
DSM

Look at the VBA help example on the FindNext method.

Just search for the string using find/FindNext, then delete the row when it
is found.

--
Regards,
Tom Ogilvy


"Dave" wrote:

How do you delete a row when it contains a text string within other text
--
DSM

I'll clarify my problem. I have hundreds of rows of data with the word "logo"
nested within other text in a cell. I need a macro to delete those rows. The
following will delete rows if the ENTIRE text in the cell matches the text in
quotes in this statement:

If Cells(iRow, 1) = "Hours of Operation" Then Rows(iRow).Delete

How can I make this work for my problem?

Thanks
--
DSM


"Tom Ogilvy" wrote:

Look at the VBA help example on the FindNext method.

Just search for the string using find/FindNext, then delete the row when it
is found.

--
Regards,
Tom Ogilvy


"Dave" wrote:

How do you delete a row when it contains a text string within other text
--
DSM

I'll clarify my problem. I have hundreds of rows of data with the word
"logo"
nested within other text in a cell. I need a macro to delete those rows.
The
following will delete rows if the ENTIRE text in the cell matches the text
in
quotes in this statement:

If Cells(iRow, 1) = "Hours of Operation" Then Rows(iRow).Delete

How can I make this work for my problem?

How about this...

If InStr(Cells(iRow, 1), "logo") 0 Then Rows(iRow).Delete

if you want your match to be case sensitive; or this, if you want a case
insensitive match...

If InStr(1, Cells(iRow, 1), "logo", vbTextCompare) 0 Then
Rows(iRow).Delete

**NOTE**
===============
InStr does not have a built-in way of distinguishing whole words. That means
if you search word were, to make it easy, "the", you would find the whole
word "the", but you would also get a match from the word "other" because
"the" is in the middle of it. So you have to be aware of the potential for
your search word to be inside other words which could give you unintended
matches when using InStr.

Rick

Good point, Rick! So if just looking for the *word* "logo" then the VBA code
would be:
If InStr(1, Cells(iRow, 1), " logo ", vbTextCompare) 0 Then
Rows(iRow).Delete
End If
Is this correct, putting a space before and after the word?

Toby

"Rick Rothstein (MVP - VB)" wrote:

I'll clarify my problem. I have hundreds of rows of data with the word
"logo"
nested within other text in a cell. I need a macro to delete those rows.
The
following will delete rows if the ENTIRE text in the cell matches the text
in
quotes in this statement:

If Cells(iRow, 1) = "Hours of Operation" Then Rows(iRow).Delete

How can I make this work for my problem?

How about this...

If InStr(Cells(iRow, 1), "logo") 0 Then Rows(iRow).Delete

if you want your match to be case sensitive; or this, if you want a case
insensitive match...

If InStr(1, Cells(iRow, 1), "logo", vbTextCompare) 0 Then
Rows(iRow).Delete

**NOTE**
===============
InStr does not have a built-in way of distinguishing whole words. That means
if you search word were, to make it easy, "the", you would find the whole
word "the", but you would also get a match from the word "other" because
"the" is in the middle of it. So you have to be aware of the potential for
your search word to be inside other words which could give you unintended
matches when using InStr.

Rick

Good point, Rick! So if just looking for the *word* "logo" then the VBA
code
would be:
If InStr(1, Cells(iRow, 1), " logo ", vbTextCompare) 0 Then
Rows(iRow).Delete
End If
Is this correct, putting a space before and after the word?

Maybe. What if logo is at the end of a sentence, then it is followed by a
period, not a space. Same goes for colons, semi-colon, question marks,
exclamation marks, opening and closing parentheses, brackets (square and
angled), etc., etc. It is not an easy problem to resolve. The key to making
it work is to be looking for very unique words or multiple words with spaces
between them... these kind of things tend to be unique enough to guarantee
no false positives. Although the multiple word ones can run into a problem
if they are split at the end of a forced line feed.

Rick

See also this page and read the tips
http://www.rondebruin.nl/delete.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Dave" wrote in message ...
I'll clarify my problem. I have hundreds of rows of data with the word "logo"
nested within other text in a cell. I need a macro to delete those rows. The
following will delete rows if the ENTIRE text in the cell matches the text in
quotes in this statement:

If Cells(iRow, 1) = "Hours of Operation" Then Rows(iRow).Delete

How can I make this work for my problem?

Thanks
--
DSM


"Tom Ogilvy" wrote:

Look at the VBA help example on the FindNext method.

Just search for the string using find/FindNext, then delete the row when it
is found.

--
Regards,
Tom Ogilvy


"Dave" wrote:

How do you delete a row when it contains a text string within other text
--
DSM

See also this page and read the tips
http://www.rondebruin.nl/delete.htm

Thanks for the link. Can I assume xlWhole makes Find search on whole words?
(I tried looking it up, but Excel's VBA help files wouldn't locate it.)

Rick

Hi Rick

Yes

You can also use use xlPart


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Rick Rothstein (MVP - VB)" wrote in message ...
See also this page and read the tips
http://www.rondebruin.nl/delete.htm

Thanks for the link. Can I assume xlWhole makes Find search on whole words?
(I tried looking it up, but Excel's VBA help files wouldn't locate it.)

Rick