G
Guest
How do you delete a row when it contains a text string within other text
Install the app
How to install the app on iOS
Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Guest
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.
Just search for the string using find/FindNext, then delete the row when it
is found.
G
Guest
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
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
R
Rick Rothstein \(MVP - VB\)
I'll clarify my problem. I have hundreds of rows of data with the word
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
"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
G
Guest
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
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
R
Rick Rothstein \(MVP - VB\)
Good point, Rick! So if just looking for the *word* "logo" then the VBA
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
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
R
Rick Rothstein \(MVP - VB\)
Good point, Rick! So if just looking for the *word* "logo" then the VBA
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
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
G
Guest
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
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
G
Guest
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
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
R
Rick Rothstein \(MVP - VB\)
Disregard my previous post. I removed the blank after " logo"
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
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
R
Ron de Bruin
See also this page and read the tips
http://www.rondebruin.nl/delete.htm
http://www.rondebruin.nl/delete.htm
R
Rick Rothstein \(MVP - VB\)
See also this page and read the tips
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 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
R
Ron de Bruin
Hi Rick
Yes
You can also use use xlPart
Yes
You can also use use xlPart
R
Rick Rothstein \(MVP - VB\)
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
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
R
Ron de Bruin
I not think there is more information then that is in the VBA help for "Range.Find Method"
G
Guest
Thanks for your link. The "VBA help" --
DSM
DSM
Ron de Bruin said:I not think there is more information then that is in the VBA help for "Range.Find Method"
G
Guest
(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?
Thanks for the link. Is their a good search term to use in VBA help to
access a list of macro functions?
R
Rick Rothstein \(MVP - VB\)
I not think there is more information then that is in the VBA help for
The description seem kind of "skimpy"<g> Oh well, maybe I'll try to find a
good reference book at the bookstore.
Rick
"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
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.