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

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.

I should have added... you can overcome these problems by running a loop
using Replace to change all of the word-limiting characters to single
spaces... then you can use the code you posted; however, this will slow
things down because of the multiple string thrashing that will take place
with each execution of a Replace function.

Rick

I've subtituted your recommended statement in the macro below and it did not
delete the rows. What is missing?

Sub DeleteRowsWithTextString()
' This macro deletes all rows on the active worksheet
' that have have a specific word in column A.
Dim iRow As Long
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1
For iRow = LastRow To 1 Step -1
If InStr(1, Cells(iRow, 1), " logo ", vbTextCompare) 0 Then
Rows(iRow).Delete
Next iRow
End Sub

Thanks
--
DSM


"Rick Rothstein (MVP - VB)" wrote:

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.

I should have added... you can overcome these problems by running a loop
using Replace to change all of the word-limiting characters to single
spaces... then you can use the code you posted; however, this will slow
things down because of the multiple string thrashing that will take place
with each execution of a Replace function.

Rick

Disregard my previous post. I removed the blank after " logo" in the macro
statement and it worked!

BTW, for my macro education, where could I find a comprehensive glossary on
the web for macro terms and their function?

Thanks Rick and Toby!

Dave
--
DSM


"Rick Rothstein (MVP - VB)" wrote:

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.

I should have added... you can overcome these problems by running a loop
using Replace to change all of the word-limiting characters to single
spaces... then you can use the code you posted; however, this will slow
things down because of the multiple string thrashing that will take place
with each execution of a Replace function.

Rick

Disregard my previous post. I removed the blank after " logo"
in the macro statement and it worked!

It won't find it in something like this though...

Company (logo goes here)

or this...

This "logo" represents....

Only you know how your data is constructed and whether other characters can
appear in front of the word. The word "logo" is relatively unique and you
might get away without using any surrounding spaces. Of course, that means
you would have to watch out for words like "logon", "logoff" and "logout".

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

Yeah, I figured if xlWhole was "whole word", then xlPart would be non-whole
words.<g

Is there a "fuller" documentation on these defined constants? Searching for
xlWhole brought up three links, one of which was "Microsoft Excel
Constants", but xlWhole (nor xlPart) was not in the long list of constants
shown.

Rick


"Ron de Bruin" wrote in message
...
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

I not think there is more information then that is in the VBA help for "Range.Find Method"


--

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


"Rick Rothstein (MVP - VB)" wrote in message ...
Yeah, I figured if xlWhole was "whole word", then xlPart would be non-whole
words.<g

Is there a "fuller" documentation on these defined constants? Searching for
xlWhole brought up three links, one of which was "Microsoft Excel
Constants", but xlWhole (nor xlPart) was not in the long list of constants
shown.

Rick


"Ron de Bruin" wrote in message
...
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

Thanks for your link. The "VBA help" --
DSM


"Ron de Bruin" wrote:

I not think there is more information then that is in the VBA help for "Range.Find Method"


--

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


"Rick Rothstein (MVP - VB)" wrote in message ...
Yeah, I figured if xlWhole was "whole word", then xlPart would be non-whole
words.<g

Is there a "fuller" documentation on these defined constants? Searching for
xlWhole brought up three links, one of which was "Microsoft Excel
Constants", but xlWhole (nor xlPart) was not in the long list of constants
shown.

Rick


"Ron de Bruin" wrote in message
...
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

(Disregard my last post - it was accidental.)

Thanks for the link. Is their a good search term to use in VBA help to
access a list of macro functions?
--
DSM


"Ron de Bruin" wrote:

I not think there is more information then that is in the VBA help for "Range.Find Method"


--

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


"Rick Rothstein (MVP - VB)" wrote in message ...
Yeah, I figured if xlWhole was "whole word", then xlPart would be non-whole
words.<g

Is there a "fuller" documentation on these defined constants? Searching for
xlWhole brought up three links, one of which was "Microsoft Excel
Constants", but xlWhole (nor xlPart) was not in the long list of constants
shown.

Rick


"Ron de Bruin" wrote in message
...
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

I not think there is more information then that is in the VBA help for
"Range.Find Method"

The description seem kind of "skimpy"<g Oh well, maybe I'll try to find a
good reference book at the bookstore.

Rick