Hi Tom,

Thank you very much indeed for your reply and assistance. Your VBA Code is in
essence what I'm looking for. Brilliant!

However, I think the phrasing of "my required Consecutive Count" was not
clear.

Tom Ogilvy wrote:
101: Rows 1,2,3 4,5 should be two

101: Rows 1,2,3 Should NOT be counted as I only want Numeric Values counted
whose consecutive appearance(s) are qualified by no more than one consecutive
appearance (per appearance) i.e; the original Numeric Value and then the
second instance.

129: Rows 1,2,3,4,5 should be one

129: Rows 1,2,3,4,5 Should NOT be counted - qualification reason as above.

Would be great if your VBA Code could reflect my required Consecutive Count?

Cheers,
Sam

anyway, this worked for me:

Sub CountData()
Dim rng As Range, rng1 As Range
Dim cnt As Range, cell As Range
Dim cell1 As Range, cnt1 As Long
Set rng = Range("Data")
Set rng1 = Range(Cells(2, 1), Cells(2, 1).End(xlDown))
For Each cell In rng1
Set rng2 = Nothing
For Each cell1 In rng
If cell1 = cell Then
If rng2 Is Nothing Then
Set rng2 = Cells(cell1.Row, 1)
Else
Set rng2 = Union(rng2, Cells(cell1.Row, 1))
End If
End If
Next
cnt1 = 0
For Each ar In rng2.Areas
If ar.Count 1 Then
cnt1 = cnt1 + 1
End If
Next
cell.Offset(0, 1).Value = cnt1 "changed Cell Offset to (0, 2)" for
column "C"
Next
End Sub

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200702/1