Find first and/or last cell that matches data

[Molasses26]

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!

[Bernie Deitrick]

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" <[email protected]> wrote in message
news:[email protected]...
>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!

[Molasses26]

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" <[email protected]> wrote in message
> news:[email protected]...
> >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!
>
>
>