title: Count Consecutive Numbers in Column
body: <p>I am looking to count the occurrences of consecutive numbers in a column and cannot seem to find a logical way to calculate this within a loop.</p>
  <p>My column of values is simply entries of 0 or 1. What I want to is count each time there is two 0's in a row, three 0's a row, four 0's in a row and so on. The maximum number of times I would expect a consecutive number is 15.</p>
  <p>Ideally, I would like the output for each occurrence entered into a table.
  I have provided a snapshot below of the column in question.</p>
  <p>My attempts so far consist of looping through the column checking for two 0's in a row, starting at row 2 but this causes issues when I have more than two 0's in a row.</p>
  <pre><code>'Check for 2
  Dim TwoCount, RowNo As Integer, LastRow As Long
  LastRow = Sheets(&quot;Data&quot;).Range(&quot;A165536&quot;).End(xlUp).Row
  TwoCount = 0
  RowNo = 2
  For i = 2 To LastRow
  If Sheets(&quot;Data&quot;).Range(&quot;H&quot; &amp; RowNo).Value = 1 Then
  RowNo = RowNo + 1
  Else
  If Sheets(&quot;Data&quot;).Range(&quot;H&quot; &amp; RowNo).Value = 0 Then
  TwoCount = 1
  RowNo = RowNo + 1
      If Sheets(&quot;Data&quot;).Range(&quot;H&quot; &amp; RowNo).Value = 0 Then
      TwoCount = 2
      RowNo = RowNo + 1
  If Sheets(&quot;Data&quot;).Range(&quot;H&quot; &amp; RowNo).Value = 1 Then
  End If
  End If
  End If
  End If
  Next i
  </code></pre>
  <p><a href="https://i.stack.imgur.com/wfQKj.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/wfQKj.png" alt="enter image description here" /></a></p>
  <p>I welcome any suggestions to how I should approach this? Whether it's easier as a formula or array formula.</p>
  <p>Desired output</p>
  <p><a href="https://i.stack.imgur.com/6ErCM.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/6ErCM.png" alt="enter image description here" /></a></p>