streak interval macro

[kraps2312]

I need assistance on a macro for counting data in a column.
Specifically looking to count the number of rows until a repeating number of 1 , 2 or 3.
In other words counting the interval for a streak of length 2.
(Note: 0 can not be a repeating number or streak but will be counted for the interval of the numbers 1, 2 or 3.)

A1,A2 is the first streak of length 2 (for #1) and it took 2 rows. So in column C, C1 would show 2 and the counting starts over.
A6,7 shows the next streak of 2 (for #3). It took 5 rows starting from A3, So 5 would go into cell C2 since there is already a value in C1.

Hope this makes sense.
photo and data below

1	2
1	5
2	2
3	2
1	4
3	2
3	2
1	2
1	3
1	3
1	2
0	2
3	
2	
2	
1	
1	
3	
3	
3	
3	
1	
2	
2	
1	
2	
2	
3	
3	
3	
3

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG07Jun18
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] p [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ray
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & rows.Count).End(xlUp))
Ray = Application.Transpose(Rng.value)
[COLOR="Navy"]For[/COLOR] n = 1 To UBound(Ray)
    c = c + 1
    [COLOR="Navy"]If[/COLOR] n > 1 [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Not Ray(n) = 0 And Ray(n) = Ray(n - 1) [COLOR="Navy"]Then[/COLOR]
            p = p + 1
            Cells(p, 3) = c
            c = 0
            Ray(n) = ""
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[kraps2312]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG07Jun18
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] p [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ray
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & rows.Count).End(xlUp))
Ray = Application.Transpose(Rng.value)
[COLOR="Navy"]For[/COLOR] n = 1 To UBound(Ray)
    c = c + 1
    [COLOR="Navy"]If[/COLOR] n > 1 [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Not Ray(n) = 0 And Ray(n) = Ray(n - 1) [COLOR="Navy"]Then[/COLOR]
            p = p + 1
            Cells(p, 3) = c
            c = 0
            Ray(n) = ""
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

Excellent solution.
I should have mentioned that the data in column A could go to A1000000
that would make the Transpose function to cause a run error.
a solution for data larger than 16,000?
Thank you