software

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: www.excelforum.com/member.php...oamp;userid=32773
View this thread: www.excelforum.com/showthread...hreadid=526040Sub selectAge12()
Dim rngSel As Range
Dim c As Range
For Each c In Range( _
Cells(2, quot;Bquot;), _
Cells(Rows.Count, quot;Bquot;))

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

quot;m.cainquot; gt; a écrit dans
le message de ...
gt;
gt; Hello,
gt;
gt; I want to select specific rows from a sequential list based on a cell
gt; value.
gt;
gt; Example:
gt;
gt; Name: Age:
gt; Mark 12
gt; Luke 15
gt; Jon 14
gt; Matthew 12
gt; Fred 12
gt;
gt; I want a method of selecting only rows where Age is 12. Note that I
gt; don't want to have to manually select each row, or even sort them by
gt; age and then select each row manually. I want an automatic method that
gt; I could use in a macro eventually.
gt;
gt;
gt; --
gt; m.cain
gt; ------------------------------------------------------------------------
gt; m.cain's Profile:
www.excelforum.com/member.php...oamp;userid=32773
gt; View this thread: www.excelforum.com/showthread...hreadid=526040
gt;
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

quot;Ardus Petusquot; wrote:

gt; Sub selectAge12()
gt; Dim rngSel As Range
gt; Dim c As Range
gt; For Each c In Range( _
gt; Cells(2, quot;Bquot;), _
gt; Cells(Rows.Count, quot;Bquot;))
gt;
gt; If c.Value = 12 Then
gt; If rngSel Is Nothing Then
gt; Set rngSel = c.EntireRow
gt; Else
gt; Set rngSel = Union(rngSel, c.EntireRow)
gt; End If
gt; End If
gt; Next c
gt; rngSel.Select
gt; End Sub
gt;
gt; HTH
gt; --
gt; AP
gt;
gt; quot;m.cainquot; gt; a écrit dans
gt; le message de ...
gt; gt;
gt; gt; Hello,
gt; gt;
gt; gt; I want to select specific rows from a sequential list based on a cell
gt; gt; value.
gt; gt;
gt; gt; Example:
gt; gt;
gt; gt; Name: Age:
gt; gt; Mark 12
gt; gt; Luke 15
gt; gt; Jon 14
gt; gt; Matthew 12
gt; gt; Fred 12
gt; gt;
gt; gt; I want a method of selecting only rows where Age is 12. Note that I
gt; gt; don't want to have to manually select each row, or even sort them by
gt; gt; age and then select each row manually. I want an automatic method that
gt; gt; I could use in a macro eventually.
gt; gt;
gt; gt;
gt; gt; --
gt; gt; m.cain
gt; gt; ------------------------------------------------------------------------
gt; gt; m.cain's Profile:
gt; www.excelforum.com/member.php...oamp;userid=32773
gt; gt; View this thread: www.excelforum.com/showthread...hreadid=526040
gt; gt;
gt;
gt;
gt;

Sub selectAge12()
Dim rngSel As Range
Dim c As Range
Dim rngRow As Range
For Each c In Range( _
Cells(2, quot;Bquot;), _
Cells(Rows.Count, quot;Bquot;).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

quot;Garthquot; gt; a écrit dans le message de
...
gt; Hi
gt;
gt; Thanks for your reply which I could benefit from.
gt;
gt; I have used the code you supplied and managed to allow input of different
gt; search strings and columns so that it can be used with larger tables.
gt; However, I would like it to only highlight the cells to the maximum width
of
gt; the selected range rather than the entire row (this will make pasting in
the
gt; same sheet easier).
gt;
gt; What changes are required so that the 'set rngsel =' rows only the
selected
gt; colums
gt;
gt; Garth
gt;
gt; quot;Ardus Petusquot; wrote:
gt;
gt; gt; Sub selectAge12()
gt; gt; Dim rngSel As Range
gt; gt; Dim c As Range
gt; gt; For Each c In Range( _
gt; gt; Cells(2, quot;Bquot;), _
gt; gt; Cells(Rows.Count, quot;Bquot;))
gt; gt;
gt; gt; If c.Value = 12 Then
gt; gt; If rngSel Is Nothing Then
gt; gt; Set rngSel = c.EntireRow
gt; gt; Else
gt; gt; Set rngSel = Union(rngSel, c.EntireRow)
gt; gt; End If
gt; gt; End If
gt; gt; Next c
gt; gt; rngSel.Select
gt; gt; End Sub
gt; gt;
gt; gt; HTH
gt; gt; --
gt; gt; AP
gt; gt;
gt; gt; quot;m.cainquot; gt; a écrit
dans
gt; gt; le message de
...
gt; gt; gt;
gt; gt; gt; Hello,
gt; gt; gt;
gt; gt; gt; I want to select specific rows from a sequential list based on a cell
gt; gt; gt; value.
gt; gt; gt;
gt; gt; gt; Example:
gt; gt; gt;
gt; gt; gt; Name: Age:
gt; gt; gt; Mark 12
gt; gt; gt; Luke 15
gt; gt; gt; Jon 14
gt; gt; gt; Matthew 12
gt; gt; gt; Fred 12
gt; gt; gt;
gt; gt; gt; I want a method of selecting only rows where Age is 12. Note that I
gt; gt; gt; don't want to have to manually select each row, or even sort them by
gt; gt; gt; age and then select each row manually. I want an automatic method that
gt; gt; gt; I could use in a macro eventually.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; m.cain
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; gt; m.cain's Profile:
gt; gt; www.excelforum.com/member.php...oamp;userid=32773
gt; gt; gt; View this thread:
www.excelforum.com/showthread...hreadid=526040
gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;