just looking for some suggestions

[Gary Keramidas]

i have quite a few rows of data, and in one column i have and id number.

the numbers will be grouped, but i'll need the first and last row for each group to do a calc.

i've looped through before, and i can get it done, but i'm just looking for suggestions. don't need code, just an idea will do.

 

[Guest]

You could use Find to locate the first and last cell. Find is usually faster
than looping. Or, since your data is grouped, you could just locate the
first cell and resize it (using countif to determine the number of rows to
resize).


Sub Test()
Const lngCriteria As Long = 3
Dim rngData As Range
Dim rngFirst As Range
Dim rngLast As Range

Set rngData = Range("A1:A200")

With rngData
Set rngFirst = .Find( _
what:=lngCriteria, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False, _
matchbyte:=False)

Set rngLast = .Find( _
what:=lngCriteria, _
after:=.Range("A1"), _
LookIn:=xlValues, _
searchorder:=xlByRows, _
searchdirection:=xlPrevious, _
MatchCase:=False, _
matchbyte:=False)
End With

If Not rngFirst Is Nothing Then
If Not rngLast Is Nothing Then
MsgBox Range(rngFirst, rngLast).Address
End If
MsgBox rngFirst.Resize(Application.CountIf(rngData, lngCriteria), 1).Address
End If
End Sub

 

[Bob Phillips]

You could create a helper column to identify start and end, perhaps

I1:=ROW()
I2:In =IF(ROW()=1,ROW(),IF(OR(I2<>I1,I2<>I3),ROW(),""))

and then just process the non-blanks of that helper column

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Gary Keramidas" <GKeramidasATmsn.com> wrote in message i have quite a few rows of data, and in one column i have and id number.

the numbers will be grouped, but i'll need the first and last row for each group to do a calc.

i've looped through before, and i can get it done, but i'm just looking for suggestions. don't need code, just an idea will do.

 

[Gary Keramidas]

thanks for the suggestions. it was a bit more complicated as there was another variable tossed in. sorry it took so long to answer, but been engrossed in this project plus all of the other normal, everyday client issues.
basically used a do while loop.

--


Gary


"Gary Keramidas" <GKeramidasATmsn.com> wrote in message i have quite a few rows of data, and in one column i have and id number.

the numbers will be grouped, but i'll need the first and last row for each group to do a calc.

i've looped through before, and i can get it done, but i'm just looking for suggestions. don't need code, just an idea will do.