counting consecutive repeated values in a column along with the range

can you help me with a script to count as well as note the position of the consecutive repeated values in a column. like one saying that 3-5, 9-11, 6001-6003 had the value 1 in them. Thanks.

The following macro should work
You will need to set up the column which has the values as a named range
("therange")
It puts the results in the column to the right of the one with the values in
it.


Public Sub countvalues()
Dim thecurrentrow%
Dim thetargetrow%
Dim thisvalue
Dim oldvalue
Dim firstflag As Boolean

firstflag = True 'checks to see if this is the first loop
thetargetrow = 1 'this is the row in the target column where you wish to
result list to start
For Each c In Range("therange") ' name the range of the column you wish to
count
therow = c.Row
thecol = c.Column
thisvalue = c.Value 'finds the current value
If thisvalue = oldvalue Then 'if this loop has the same value as the
previous one it works out what the last row no was
lastrow = therow
lastcol = thecol
Else
If Not firstflag Then
'when the current cell value differs from the previous cell value it
'writes out the start row - end row (value of range)
'note it does this when there is only a single itteration of the
value
'if you want to stop this behaviour put an If then else statement
around the next two lines
'if lastrow < firstrow then....else...endif
Range(Cells(thetargetrow, thecol + 1), Cells(thetargetrow, thecol +
1)).Value = firstrow & " - " & IIf(lastrow > firstrow, lastrow, firstrow) &
"(" & oldvalue & ")"
thetargetrow = thetargetrow + 1
Else
'picks up the endrow values for the initial loop
lastrow = therow
lastcol = thecol
firstflag = False
End If
firstrow = therow
firstcol = thecol
End If
oldvalue = thisvalue



Next c
End Sub


"g s" <[email protected]> wrote in message
news:[email protected]...
>
> can you help me with a script to count as well as note the position of
> the consecutive repeated values in a column. like one saying that 3-5,
> 9-11, 6001-6003 had the value 1 in them. Thanks.
>
>
> --
> g s
> ------------------------------------------------------------------------
> g s's Profile:
http://www.excelforum.com/member.php...o&userid=24263
> View this thread: http://www.excelforum.com/showthread...hreadid=469983
>