# Help with a STREAK formula

#### c0087

##### New Member
- 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

 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>

### Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

#### Rick Rothstein

##### MrExcel MVP
Here is a macro that should work for you...
Code:
``````[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

##### New Member
Here is a macro that should work for you...
Code:
``````[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

##### MrExcel MVP
Sorry, bad assumption on my part. Give the following revision a try (this one should work correctly for you)...
Code:
``````[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

##### New Member
Sorry, bad assumption on my part. Give the following revision a try (this one should work correctly for you)...
Code:
``````[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

Replies
23
Views
2K
Replies
9
Views
690
Replies
2
Views
370
Replies
3
Views
51
Replies
11
Views
158