Help with a STREAK formula

[c0087]

- I have a streak in column B
- I need a macro/formula to only return the end of each streak (no zeros) in column D
- The result should look like column D

Thanks in advance

A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
	1		1
	0		4
	1		2
	2		3
	3
	4
	0
	1
	2
	0
	0
	0
	1
	2
	3

<tbody>
</tbody>

 

[Rick Rothstein]

Here is a macro that should work for you...

[table="width: 500"]
[tr]
	[td]Sub Streak()
  Dim X As Long, Streaks() As String
  Streaks = Split(Application.Trim(Replace(Join(Application.Transpose(Range("B1", Cells(Rows.Count, "B").End(xlUp))), ""), 0, " ")))
  For X = 0 To UBound(Streaks)
    Cells(X + 1, "D").Value = Len(Streaks(X))
  Next
End Sub[/td]
[/tr]
[/table]

 

[c0087]

Here is a macro that should work for you...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub Streak()
  Dim X As Long, Streaks() As String
  Streaks = Split(Application.Trim(Replace(Join(Application.Transpose(Range("B1", Cells(Rows.Count, "B").End(xlUp))), ""), 0, " ")))
  For X = 0 To UBound(Streaks)
    Cells(X + 1, "D").Value = Len(Streaks(X))
  Next
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for the quick reply
It almost works. Below is a copy from my actual spreadsheet
As you can see, it should put out 12,1 rather than 10,4

			1
W	L		1
1	0		3
0	1		10
1	0		4
0	1		1
0	2		6
0	3		2
1	0		5
2	0		4
3	0		6
0	1		10
1	0		12
2	0		4
3	0		4
4	0		7
5	0		2
6	0		7
7	0		1
8	0		1
9	0		1
10	0		2
11	0		3
12	0		1
0	1		4
1	0		1
0	1		2
1	0
2	0
3	0
4	0
5	0
6	0
0	1
1	0
2	0
0	1
1	0
2	0
3	0
4	0
5	0
0	1
0	2
1	0
2	0
3	0
4	0
0	1
1	0
2	0
3	0
4	0
5	0
6	0
0	1
1	0
2	0
3	0
4	0
5	0
6	0
7	0
8	0
9	0
10	0
11	0
12	0
13	0
14	0
15	0
16	0
0	1
1	0
2	0
3	0
4	0
0	1
1	0
2	0
3	0
4	0
0	1
1	0
2	0
3	0
4	0
5	0
6	0
7	0
0	1
0	2
1	0
2	0
0	1
1	0
2	0
3	0
4	0
5	0
6	0
7	0
0	1
1	0
0	1
0	2
0	3
1	0
0	1
1	0
0	1
1	0
2	0
0	1
1	0
2	0
3	0
0	1
0	2
0	3
0	4
1	0
0	1
0	2
0	3
1	0
2	0
3	0
4	0
0	1
1	0
0	1
1	0
2	0
0	0
0	0

<colgroup><col width="67" span="4" style="width:50pt"> </colgroup><tbody>
</tbody>

 

[Rick Rothstein]

Sorry, bad assumption on my part. Give the following revision a try (this one should work correctly for you)...

[table="width: 500"]
[tr]
	[td]Sub Streak()
  Dim X As Long, Streaks() As String
  Streaks = Split(Application.Trim(Join(Application.Transpose(Evaluate("IF(B2:B" & Cells(Rows.Count, "B").End(xlUp).Row & "=0,"" "",""X"")")), "")))
  For X = 0 To UBound(Streaks)
    Cells(X + 1, "D").Value = Len(Streaks(X))
  Next
End Sub[/td]
[/tr]
[/table]

 

[c0087]

Sorry, bad assumption on my part. Give the following revision a try (this one should work correctly for you)...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub Streak()
  Dim X As Long, Streaks() As String
  Streaks = Split(Application.Trim(Join(Application.Transpose(Evaluate("IF(B2:B" & Cells(Rows.Count, "B").End(xlUp).Row & "=0,"" "",""X"")")), "")))
  For X = 0 To UBound(Streaks)
    Cells(X + 1, "D").Value = Len(Streaks(X))
  Next
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Thank you sir - works perfectly