Select targeted rows
 

What I'd like to do is run code that searches a column for a value, and when
found, selects the row the column is in. I've set up a sort so that all rows
with the same value are grouped together, so then I'd like the code to
continue to select each row until it's reached the end of the grouped rows.

I can write the code to find the identifying value and continue to search
down the column. What I don't know how to do is to select the row, and then
extend the selection as the search continues. Any help with that would very,
very appreciated.

Thanks in advance to anyone who can help with this.

Dan

Select targeted rows
 

Here is some code. Note that it does not require the column to be sorted...

Sub SelectRows()
Dim wks As Worksheet
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String
Dim rngToSearch As Range
Dim strToFind As String

strToFind = "this" 'Change This
Set wks = Sheets("Sheet1") 'Change this
Set rngToSearch = wks.Columns("A") 'Change this
Set rngFound = rngToSearch.Find(What:=strToFind, _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry nothing was found"
Else
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
rngFoundAll.EntireRow.Select
End If
End Sub
--
HTH...

Jim Thomlinson


"danhattan" wrote:

What I'd like to do is run code that searches a column for a value, and when
found, selects the row the column is in. I've set up a sort so that all rows
with the same value are grouped together, so then I'd like the code to
continue to select each row until it's reached the end of the grouped rows.

I can write the code to find the identifying value and continue to search
down the column. What I don't know how to do is to select the row, and then
extend the selection as the search continues. Any help with that would very,
very appreciated.

Thanks in advance to anyone who can help with this.

Dan

Select targeted rows
 

Thanks, because this is very close to what I was looking for, and would be
except for one thing. The column I want to search is using a formula to set
an indicator based on a value in a column to the left.

When I run your code and adjust the variables, I initially got the message
box saying "Sorry nothing was found". On a hunch, I converted the formulas to
values. After that, the code correctly recognized the text and selected the
rows. (I then added a final statement to delete the selected rows, which is
my ultimate goal.)

So, how do I adjust the code to correctly recognize the result of the
formulas? I can write code to convert the formulas to values, then paste the
formulas back each day, but that just seems a bit inelegant.

Any thoughts? Also, thanks much for the solution. It even managed to select
non-contiguous blocks, which is pretty cool. But I guess you knew that. :)

"Jim Thomlinson" wrote:

Here is some code. Note that it does not require the column to be sorted...

Sub SelectRows()
Dim wks As Worksheet
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String
Dim rngToSearch As Range
Dim strToFind As String

strToFind = "this" 'Change This
Set wks = Sheets("Sheet1") 'Change this
Set rngToSearch = wks.Columns("A") 'Change this
Set rngFound = rngToSearch.Find(What:=strToFind, _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry nothing was found"
Else
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
rngFoundAll.EntireRow.Select
End If
End Sub
--
HTH...

Jim Thomlinson


"danhattan" wrote:

What I'd like to do is run code that searches a column for a value, and when
found, selects the row the column is in. I've set up a sort so that all rows
with the same value are grouped together, so then I'd like the code to
continue to select each row until it's reached the end of the grouped rows.

I can write the code to find the identifying value and continue to search
down the column. What I don't know how to do is to select the row, and then
extend the selection as the search continues. Any help with that would very,
very appreciated.

Thanks in advance to anyone who can help with this.

Dan

Select targeted rows
 

Change LookIn:=xlFormulas to LookIn:=xlValues
--
HTH...

Jim Thomlinson


"danhattan" wrote:

Thanks, because this is very close to what I was looking for, and would be
except for one thing. The column I want to search is using a formula to set
an indicator based on a value in a column to the left.

When I run your code and adjust the variables, I initially got the message
box saying "Sorry nothing was found". On a hunch, I converted the formulas to
values. After that, the code correctly recognized the text and selected the
rows. (I then added a final statement to delete the selected rows, which is
my ultimate goal.)

So, how do I adjust the code to correctly recognize the result of the
formulas? I can write code to convert the formulas to values, then paste the
formulas back each day, but that just seems a bit inelegant.

Any thoughts? Also, thanks much for the solution. It even managed to select
non-contiguous blocks, which is pretty cool. But I guess you knew that. :)

"Jim Thomlinson" wrote:

Here is some code. Note that it does not require the column to be sorted...

Sub SelectRows()
Dim wks As Worksheet
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String
Dim rngToSearch As Range
Dim strToFind As String

strToFind = "this" 'Change This
Set wks = Sheets("Sheet1") 'Change this
Set rngToSearch = wks.Columns("A") 'Change this
Set rngFound = rngToSearch.Find(What:=strToFind, _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry nothing was found"
Else
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
rngFoundAll.EntireRow.Select
End If
End Sub
--
HTH...

Jim Thomlinson


"danhattan" wrote:

What I'd like to do is run code that searches a column for a value, and when
found, selects the row the column is in. I've set up a sort so that all rows
with the same value are grouped together, so then I'd like the code to
continue to select each row until it's reached the end of the grouped rows.

I can write the code to find the identifying value and continue to search
down the column. What I don't know how to do is to select the row, and then
extend the selection as the search continues. Any help with that would very,
very appreciated.

Thanks in advance to anyone who can help with this.

Dan

Select targeted rows
 

Perfect. That's exactly the answer, even if it is a bit counter-intuitive. On
the surface, it seems that if one wants to search through formulas, one would
use xlFormulas.

But again, thanks for the help. This is a great answer. I'm trying to fully
automate a spreadsheet for a supervisor here who isn't comfortable with
Excel, and this will do wonders for her mornings.

Have a great Friday!

"Jim Thomlinson" wrote:

Change LookIn:=xlFormulas to LookIn:=xlValues
--
HTH...

Jim Thomlinson


"danhattan" wrote:

Thanks, because this is very close to what I was looking for, and would be
except for one thing. The column I want to search is using a formula to set
an indicator based on a value in a column to the left.

When I run your code and adjust the variables, I initially got the message
box saying "Sorry nothing was found". On a hunch, I converted the formulas to
values. After that, the code correctly recognized the text and selected the
rows. (I then added a final statement to delete the selected rows, which is
my ultimate goal.)

So, how do I adjust the code to correctly recognize the result of the
formulas? I can write code to convert the formulas to values, then paste the
formulas back each day, but that just seems a bit inelegant.

Any thoughts? Also, thanks much for the solution. It even managed to select
non-contiguous blocks, which is pretty cool. But I guess you knew that. :)

"Jim Thomlinson" wrote:

Here is some code. Note that it does not require the column to be sorted...

Sub SelectRows()
Dim wks As Worksheet
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String
Dim rngToSearch As Range
Dim strToFind As String

strToFind = "this" 'Change This
Set wks = Sheets("Sheet1") 'Change this
Set rngToSearch = wks.Columns("A") 'Change this
Set rngFound = rngToSearch.Find(What:=strToFind, _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry nothing was found"
Else
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
rngFoundAll.EntireRow.Select
End If
End Sub
--
HTH...

Jim Thomlinson


"danhattan" wrote:

What I'd like to do is run code that searches a column for a value, and when
found, selects the row the column is in. I've set up a sort so that all rows
with the same value are grouped together, so then I'd like the code to
continue to select each row until it's reached the end of the grouped rows.

I can write the code to find the identifying value and continue to search
down the column. What I don't know how to do is to select the row, and then
extend the selection as the search continues. Any help with that would very,
very appreciated.

Thanks in advance to anyone who can help with this.

Dan