Selecting rows based on cell entries
 

Hello,

I want to select specific rows from a sequential list based on a cell
value.

Example:

Name: Age:
Mark 12
Luke 15
Jon 14
Matthew 12
Fred 12

I want a method of selecting only rows where Age is 12. Note that I
don't want to have to manually select each row, or even sort them by
age and then select each row manually. I want an automatic method that
I could use in a macro eventually.


--
m.cain
------------------------------------------------------------------------
m.cain's Profile: http://www.excelforum.com/member.php...o&userid=32773
View this thread: http://www.excelforum.com/showthread...hreadid=526040

Selecting rows based on cell entries
 

Sub selectAge12()
Dim rngSel As Range
Dim c As Range
For Each c In Range( _
Cells(2, "B"), _
Cells(Rows.Count, "B"))

If c.Value = 12 Then
If rngSel Is Nothing Then
Set rngSel = c.EntireRow
Else
Set rngSel = Union(rngSel, c.EntireRow)
End If
End If
Next c
rngSel.Select
End Sub

HTH
--
AP

"m.cain" a écrit dans
le message de ...

Hello,

I want to select specific rows from a sequential list based on a cell
value.

Example:

Name: Age:
Mark 12
Luke 15
Jon 14
Matthew 12
Fred 12

I want a method of selecting only rows where Age is 12. Note that I
don't want to have to manually select each row, or even sort them by
age and then select each row manually. I want an automatic method that
I could use in a macro eventually.


--
m.cain
------------------------------------------------------------------------
m.cain's Profile:
http://www.excelforum.com/member.php...o&userid=32773
View this thread: http://www.excelforum.com/showthread...hreadid=526040

Selecting rows based on cell entries
 

Hi

Thanks for your reply which I could benefit from.

I have used the code you supplied and managed to allow input of different
search strings and columns so that it can be used with larger tables.
However, I would like it to only highlight the cells to the maximum width of
the selected range rather than the entire row (this will make pasting in the
same sheet easier).

What changes are required so that the 'set rngsel =' rows only the selected
colums

Garth

"Ardus Petus" wrote:

Sub selectAge12()
Dim rngSel As Range
Dim c As Range
For Each c In Range( _
Cells(2, "B"), _
Cells(Rows.Count, "B"))

If c.Value = 12 Then
If rngSel Is Nothing Then
Set rngSel = c.EntireRow
Else
Set rngSel = Union(rngSel, c.EntireRow)
End If
End If
Next c
rngSel.Select
End Sub

HTH
--
AP

"m.cain" a écrit dans
le message de ...

Hello,

I want to select specific rows from a sequential list based on a cell
value.

Example:

Name: Age:
Mark 12
Luke 15
Jon 14
Matthew 12
Fred 12

I want a method of selecting only rows where Age is 12. Note that I
don't want to have to manually select each row, or even sort them by
age and then select each row manually. I want an automatic method that
I could use in a macro eventually.


--
m.cain
------------------------------------------------------------------------
m.cain's Profile:
http://www.excelforum.com/member.php...o&userid=32773
View this thread: http://www.excelforum.com/showthread...hreadid=526040

Selecting rows based on cell entries
 

Sub selectAge12()
Dim rngSel As Range
Dim c As Range
Dim rngRow As Range
For Each c In Range( _
Cells(2, "B"), _
Cells(Rows.Count, "B").End(xlUp))

If c.Value = 12 Then
Set rngRow = Range( _
Cells(c.Row, 1), _
Cells(c.Row, Columns.Count).End(xlToLeft))
If rngSel Is Nothing Then
Set rngSel = rngRow
Else
Set rngSel = Union(rngSel, rngRow)
End If
End If
Next c
rngSel.Select
End Sub

HTH
--
AP

"Garth" a écrit dans le message de
...
Hi

Thanks for your reply which I could benefit from.

I have used the code you supplied and managed to allow input of different
search strings and columns so that it can be used with larger tables.
However, I would like it to only highlight the cells to the maximum width
of
the selected range rather than the entire row (this will make pasting in
the
same sheet easier).

What changes are required so that the 'set rngsel =' rows only the
selected
colums

Garth

"Ardus Petus" wrote:

Sub selectAge12()
Dim rngSel As Range
Dim c As Range
For Each c In Range( _
Cells(2, "B"), _
Cells(Rows.Count, "B"))

If c.Value = 12 Then
If rngSel Is Nothing Then
Set rngSel = c.EntireRow
Else
Set rngSel = Union(rngSel, c.EntireRow)
End If
End If
Next c
rngSel.Select
End Sub

HTH
--
AP

"m.cain" a écrit
dans
le message de
...

Hello,

I want to select specific rows from a sequential list based on a cell
value.

Example:

Name: Age:
Mark 12
Luke 15
Jon 14
Matthew 12
Fred 12

I want a method of selecting only rows where Age is 12. Note that I
don't want to have to manually select each row, or even sort them by
age and then select each row manually. I want an automatic method that
I could use in a macro eventually.


--
m.cain

------------------------------------------------------------------------
m.cain's Profile:
http://www.excelforum.com/member.php...o&userid=32773
View this thread:
http://www.excelforum.com/showthread...hreadid=526040