Selecting rows based on cell entries

[m.cain]

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?action=getinfo&userid=32773
View this thread: http://www.excelforum.com/showthread.php?threadid=526040

[Ardus Petus]

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" <m.cain.256681_...@excelforum-nospam.com> a écrit dans
le message de news:m.cain.256681_...@excelforum-nospam.com...

[Garth]

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]

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" <Ga...@discussions.microsoft.com> a écrit dans le message de
news:92A03AD4-8725-4965...@microsoft.com...