Have a huge spreadsheet with cells populated with lots of text. Would like
to isolate or highlight rows containing a single term, e.g., "aspirin" with
one click or function as opposed to doing a find command and hitting
everything one at a time. Any suggestions? Thanks.

You are a little vague with exacty what you want to do with the found cells.
That being said here is some code that selects the cells containing the text
you input. To run this select the range (row, column, range or entire sheet)
that you want to search. Run the code. It will ask you to input the text to
search for. It will then select the cells containing that text.

Public Sub FindTextItems()
Dim wks As Worksheet
Dim rngCurrent As Range
Dim rngFirst As Range
Dim rngConsolidated As Range
Dim rngToSearch As Range
Dim strToFind As String

strToFind = InputBox("Please enter the text to search for.", "Find Text")

Set wks = ActiveSheet
Set rngToSearch = Intersect(Selection, wks.UsedRange)
Set rngCurrent = rngToSearch.Find(strToFind)

If rngCurrent Is Nothing Then
MsgBox strToFind & " was not found in the selected area.", _
vbOKOnly, "Not Found"
Else
Set rngConsolidated = rngCurrent
Set rngFirst = rngCurrent
Do
Set rngConsolidated = Union(rngCurrent, rngConsolidated)
Set rngCurrent = rngToSearch.FindNext(rngCurrent)
Loop Until rngCurrent.Address = rngFirst.Address
rngConsolidated.Select
End If
End Sub

--
HTH...

Jim Thomlinson


"williejoeshaver" wrote:

Have a huge spreadsheet with cells populated with lots of text. Would like
to isolate or highlight rows containing a single term, e.g., "aspirin" with
one click or function as opposed to doing a find command and hitting
everything one at a time. Any suggestions? Thanks.

Thanks for the help. Sorry for being vague. Basically this is a customer
list with customer name, acct #, and some notes in each column. We want to
be able to pick out only the customers for whom a specific word appears in
their notes (e.g., "this customer called about BIAXIN"). If we are able to
highlight/isolate those customers (from the thousands who do not meet that
single criterion), we can create an easily targeted call list. In other
words, I want to pick the 1000 rows that contain a cell with that text from
the 10000 rows total without searching one by one (and the term we're
searching for is buried in lots of text so we can't simply sort).

"Jim Thomlinson" wrote:

You are a little vague with exacty what you want to do with the found cells.
That being said here is some code that selects the cells containing the text
you input. To run this select the range (row, column, range or entire sheet)
that you want to search. Run the code. It will ask you to input the text to
search for. It will then select the cells containing that text.

Public Sub FindTextItems()
Dim wks As Worksheet
Dim rngCurrent As Range
Dim rngFirst As Range
Dim rngConsolidated As Range
Dim rngToSearch As Range
Dim strToFind As String

strToFind = InputBox("Please enter the text to search for.", "Find Text")

Set wks = ActiveSheet
Set rngToSearch = Intersect(Selection, wks.UsedRange)
Set rngCurrent = rngToSearch.Find(strToFind)

If rngCurrent Is Nothing Then
MsgBox strToFind & " was not found in the selected area.", _
vbOKOnly, "Not Found"
Else
Set rngConsolidated = rngCurrent
Set rngFirst = rngCurrent
Do
Set rngConsolidated = Union(rngCurrent, rngConsolidated)
Set rngCurrent = rngToSearch.FindNext(rngCurrent)
Loop Until rngCurrent.Address = rngFirst.Address
rngConsolidated.Select
End If
End Sub

--
HTH...

Jim Thomlinson


"williejoeshaver" wrote:

Have a huge spreadsheet with cells populated with lots of text. Would like
to isolate or highlight rows containing a single term, e.g., "aspirin" with
one click or function as opposed to doing a find command and hitting
everything one at a time. Any suggestions? Thanks.