Count consecutive repeats

[EV]

I'm looking to count the number of consecutive occurances of a 1
without a zero in a table but only if it is a new run. i.e.

Data Result
0 0
1 1
0 0
1 0
1 0
1 3
0 0


A VBA solution is ok

Evan
Australia

 

[Greg Glynn]

Evan: Select the range you're counting before you run the macro.

Sub MyCounter()
OneCounter = 0
For Each c In Selection
If c.Value = 0 Then
c.Offset(0, 1).Value = 0
OneCounter = 0
Else
OneCounter = OneCounter + 1
If c.Offset(1, 0).Value = 0 Then
c.Offset(0, 1) = OneCounter
Else
c.Offset(0, 1) = 0
End If
End If
Next c
End Sub

[Tested OK]

Select the range you're counting before you run the macro.