Select rows based on keyword?

[Guest]

Hello,

I've been looking around a bit, but haven't found anything so maybe somebody
here has an idea.

I have a sheet with thousands of rows, and I want to delete a few hundred
based on the presence of a particular word in a specific column.
So ideally, I'll specify the word, and then all the rows get selected which
contain that word in that one column (C in my case), and I can delete all
those rows.
That may be difficult/impossible, but maybe it's easier to change the
background of the cells containing that keyword?

Any ideas would be greatly appreciated.
Thank you.

 

[Guest]

See if something along the lines of this macro point you in the right
direction:

Sub SelectRows()

Dim varVal As Variant
Dim lngOffset As Long
Dim strRowNum As String
Dim lngRow As Long

varVal = Range("C1").Value

Do Until varVal = ""
If varVal = "keyword" Then
lngRow = lngOffset + 1
strRowNum = strRowNum & lngRow & ":" & lngRow & ","
End If
lngOffset = lngOffset + 1
varVal = Range("C1").Offset(lngOffset).Value
Loop

If Len(strRowNum) > 0 Then
strRowNum = Left$(strRowNum, Len(strRowNum) - 1)
Range(strRowNum).Select
End If


End Sub

 

[Zone]

This will delete all rows containing the specified word in column C. Copy
the code and paste in a standard module. HTH, James

Sub RowsOut()
Dim k As Long, myWord As String
myWord = InputBox("Word to find?")
If myWord <> "" Then
For k = Cells(Rows.Count, "c").End(xlUp).Row To 1 Step -1
If CStr(Cells(k, "c")) = myWord Then Rows(k).EntireRow.Delete
Next k
End If
End Sub

 

[Guest]

Thank you, Kevin.

I ran the macro, but nothing happened. Maybe I should have mentioned that
the word I want to base the selection upon is not the only word in the cell?
Suppose I have cells that say Green Apple or Red Apple or Yellow Apple. Then
I'd like to be able to select all cells with Green in it (the cells contain
more than 2 words, something like Green Apple in a Bag or Green Apple in a
Large Case and Green Apple in a Small Case etc.)

 

[Guest]

Oh, that would be cool if it worked!

Sadly, nothing is happening again, not even an error message. Even tried it
with a new worksheet and dummy data.

I created a new macro, pasted the code into it, and ran it.

 

[Rick Rothstein \(MVP - VB\)]

Select Edit/Find from Excel's menu. Click the "Options" button on the dialog
box that appears. Now, type the word you want to find in the "Find what"
field, check "Match case" ONLY IF that is a condition that has to be met,
uncheck the "Match entire cell contents" (based on your latest answer to
Kevin), choose "Sheet" from the "Within" dropdown, select "By Columns" from
the "Search" field and "Values" from the "Look In" drop down. Once that is
done, press the "Find All" button. In the list that appears (which is listed
in order by spreadsheet column addresses in the Cell column of the list),
click the first Column C address and then Shift-Click the last Column C
address. Doing this will select all the cells in column C with your "find
word" in them. Close the Find dialog box and the cells will all remain
selected. Next, select Edit/Delete from Excel's menu. Select the "Entire
Row" option and then click OK. The rows you wanted to delete will be gone.

Rick

 

[Guest]

Hello Rick,

That works wonderfully, thank you very much. This is going to make my life a
lot easier.

I guess that answers my question. It would be great though if you guys were
willing to spend some more time on this and make the makros work. I really
like Zone's approach with the dialog box.

 

[Dave Peterson]

Another option that may work for you....

Select the column that may contain the word
data|filter|autofilter
Use a custom filter to show only those rows that contain the word you're looking
for (or equals if there's nothing else in those cells).

Delete the visible rows
remove the data|filter

 

[Guest]

Hello Dave,

Thank you for your idea.
That works as well, although I don't like it as much as Rick's method
because I have to select the rows manually and then delete them instead of
just pressing Ctrl-A to do it for me.
Still, it's a lot better than what I had been doing, which is selecting rows
by hand.

 

[Rick Rothstein \(MVP - VB\)]

That works wonderfully, thank you very much. This is going to make my life

a
lot easier.

You are welcome. There is one caveat with the method I posted... if the word
you are searching for appears as part of another word, it will be found
also. By that, I mean, if you were searching for the word "vat", words like
"Vatican" and "private" would be found also. If your word list could contain
such words, then, after selecting all of the Column C cells (like I
described in my first post), look at the "Value" column to see if you
grabbed any cells with embedded words in them... if you find any, simply
Ctrl+Click the entry to deselect it (all of the other selections will
remain). Once you have done that, proceed as previously described.

I guess that answers my question. It would be great though if you guys
were
willing to spend some more time on this and make the makros work. I really
like Zone's approach with the dialog box.

Here is a modification to Zone's code which will do what you asked...

Sub RowsOut()
Dim k As Long, myWord As String
myWord = InputBox("Word to find?")
If myWord <> "" Then
For k = Cells(Rows.Count, "c").End(xlUp).Row To 1 Step -1
If InStr(1, CStr(Cells(k, "c")), myWord, vbTextCompare) Then
Rows(k).EntireRow.Delete
End If
Next k
End If
End Sub

Note, though, this has the same "defect" as the Edit/Find/Delete method I
posted earlier and described above... rows with embedded words in the cells
in the column being searched will be deleted... but you won't be able to
deselect them as described above. Yes, we can add a UserForm and put a
ListBox on it to display the found cell values, allow you to select/deselect
the rows you want, but at this point, you have done nothing more than
duplicate the built-in Edit/Find/Delete method I posted for you.

Rick

 

[Guest]

Fantastic, thanks a lot!

Thank you also for the warning. Right now this won't be a problem, but it's
good to know about this limitation if I want to use this some other time.

I have to say though, such a user form would still be good simply because
with a macro, all one has to do is click on a button.

 

[Guest]

I decided to put the warning in the dialog box. Can that be formatted? I'd
like to have a line break and possibly colour.

Thanks.

 

[Guest]

I found information on creating a line break:

"[text]" & vbCrLf & "[text]"

 

[Zone]

Niniel, I changed to code to find the word in the cell, since apparently
there are other words in the cell. This should work better if you haven't
already got a good solution. James

Sub RowsOut()
Dim k As Long, myWord As String
myWord = InputBox("Word to find?")
If myWord <> "" Then
For k = Cells(Rows.Count, "c").End(xlUp).Row To 1 Step -1
If VarType(Cells(k, "c")) = vbString Then
If InStr(Cells(k, "c"), myWord) > 0 Then
Rows(k).EntireRow.Delete
End If
Next k
End If
End Sub

 

[Guest]

Thank you for taking the time to update your code.
Rick already fixed me up; it works very nicely.