I have a spreadsheet like this:
A B C D
1 ID Loc Date Rep
2 25 a4 4/6/05 Dave
3 28 a5 4/6/05 Dave
4 32 a7 4/6/05 Dave
5 76 a4 4/6/05 Fred
6 84 a5 4/6/05 Fred
7 66 a7 4/6/05 Fred
8 54 a3 4/6/05 Jack
9 22 a3 4/6/05 Jack

I want to locate the address of the cell in column A on the row containing
the FIRST appearance of "Fred" (A5 in this case) and also the address of the
cell containing the LAST appearance of "Fred" (D7 in this case). I want to
use these values in a range reference so I can sort different sections of the
spread sheet in different orders.
Thanks!

Molasses,

The block of 3 rows by 4 columns of cells with Fred in column D can be found
by:

Dim myCell As Range
Set myCell = Range("D:D").Find("Fred")
MsgBox Range(myCell, Cells(myCell.Row + _
Application.CountIf(Range("D:D"), "Fred") - 1, 1)).Address

Note that this requires the column D be sorted.

HTH,
Bernie
MS Excel MVP


"Molasses26" wrote in message
...
I have a spreadsheet like this:
A B C D
1 ID Loc Date Rep
2 25 a4 4/6/05 Dave
3 28 a5 4/6/05 Dave
4 32 a7 4/6/05 Dave
5 76 a4 4/6/05 Fred
6 84 a5 4/6/05 Fred
7 66 a7 4/6/05 Fred
8 54 a3 4/6/05 Jack
9 22 a3 4/6/05 Jack

I want to locate the address of the cell in column A on the row containing
the FIRST appearance of "Fred" (A5 in this case) and also the address of
the
cell containing the LAST appearance of "Fred" (D7 in this case). I want
to
use these values in a range reference so I can sort different sections of
the
spread sheet in different orders.
Thanks!

Fantastic!! It works perfectly - Thanks!!!

"Bernie Deitrick" wrote:

Molasses,

The block of 3 rows by 4 columns of cells with Fred in column D can be found
by:

Dim myCell As Range
Set myCell = Range("D:D").Find("Fred")
MsgBox Range(myCell, Cells(myCell.Row + _
Application.CountIf(Range("D:D"), "Fred") - 1, 1)).Address

Note that this requires the column D be sorted.

HTH,
Bernie
MS Excel MVP


"Molasses26" wrote in message
...
I have a spreadsheet like this:
A B C D
1 ID Loc Date Rep
2 25 a4 4/6/05 Dave
3 28 a5 4/6/05 Dave
4 32 a7 4/6/05 Dave
5 76 a4 4/6/05 Fred
6 84 a5 4/6/05 Fred
7 66 a7 4/6/05 Fred
8 54 a3 4/6/05 Jack
9 22 a3 4/6/05 Jack

I want to locate the address of the cell in column A on the row containing
the FIRST appearance of "Fred" (A5 in this case) and also the address of
the
cell containing the LAST appearance of "Fred" (D7 in this case). I want
to
use these values in a range reference so I can sort different sections of
the
spread sheet in different orders.
Thanks!