Find first and last instance

Hi all,

I have a column containing Month names. Each month is grouped together. IE:
March
March
April
April
April
May
May
May
May
etc etc

how do i find the row of the first instance of a certain month, and also the
row of the last instance of the same month


tia

J

Sub EFGH()
Dim rng As Range, rng1 As Range
Dim sAddr As String
Set rng = Columns(1).Find("April")
sAddr = rng.Address
If Not rng Is Nothing Then
Do
Set rng1 = rng
Set rng = Columns(1).FindNext(rng)
Loop While rng.Address <> sAddr
Range(rng, rng1).Select
End If

End Sub


--
Regards,
Tom Ogilvy


"Gixxer_J_97" <[email protected]> wrote in message
news:[email protected]...
> Hi all,
>
> I have a column containing Month names. Each month is grouped together.
IE:
> March
> March
> April
> April
> April
> May
> May
> May
> May
> etc etc
>
> how do i find the row of the first instance of a certain month, and also
the
> row of the last instance of the same month
>
>
> tia
>
> J

Hi,
you can use the find function and tell it to search within the whole range
(say rgSrc) as follow:
- for the First occurence: start search after the last cell of the range
rgSrc and search in Direction forward (xlNext)
- for the Last occurence: start search after the first cell of the range
rgSrc and search in Direction backward (xlPrevious)

Sub test()
Dim rgSrc As Range, rgFirst As Range, rgLast As Range

Set rgSrc = Range("A2:A120")

Set rgFirst = rgSrc.Find(what:="March",
after:=rgSrc.Cells(rgSrc.Cells.Count) _
,LookIn:=xlValues, lookat:=xlWhole, searchdirection:=xlNext)
Set rgLast = rgSrc.Find(what:="March", after:=rgSrc.Cells(1), _
LookIn:=xlValues, lookat:=xlWhole, searchdirection:=xlPrevious)
Debug.Print rgFirst.Address, rgLast.Address
End Sub
--
Regards,
Sébastien
<http://www.ondemandanalysis.com>


"Gixxer_J_97" wrote:

> Hi all,
>
> I have a column containing Month names. Each month is grouped together. IE:
> March
> March
> April
> April
> April
> May
> May
> May
> May
> etc etc
>
> how do i find the row of the first instance of a certain month, and also the
> row of the last instance of the same month
>
>
> tia
>
> J

Thanks Tom!

J

"Tom Ogilvy" wrote:

> Sub EFGH()
> Dim rng As Range, rng1 As Range
> Dim sAddr As String
> Set rng = Columns(1).Find("April")
> sAddr = rng.Address
> If Not rng Is Nothing Then
> Do
> Set rng1 = rng
> Set rng = Columns(1).FindNext(rng)
> Loop While rng.Address <> sAddr
> Range(rng, rng1).Select
> End If
>
> End Sub
>
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Gixxer_J_97" <[email protected]> wrote in message
> news:[email protected]...
> > Hi all,
> >
> > I have a column containing Month names. Each month is grouped together.
> IE:
> > March
> > March
> > April
> > April
> > April
> > May
> > May
> > May
> > May
> > etc etc
> >
> > how do i find the row of the first instance of a certain month, and also
> the
> > row of the last instance of the same month
> >
> >
> > tia
> >
> > J
>
>
>