Selecting rows based on cell entries

M

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.
 
A

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
 
G

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 said:
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
Click to expand...
 
A

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
 
Top