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" wrote in message
...
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" wrote in message
...
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