Move from one cell to another and count the number of rows in between two data

Question

I have got one column with 250 rows. The data fed is 0 and 1 randomly in these rows. I need to find the number of row between any two 1's. I need to write a macro for the same.For ex: if my column is as follow:

       A1 0        B1 2
       A2 0        B2 2
       A3 0        
       A4 1
       A5 0 
       A6 0 
       A7 1
       A8 0 
       A9 0 
      A10 1

What I want is to check the entire column from A1 to A10. Starting from A1, my program would check for '1', it found in A4, it will continue to move down until it finds another '1'. Here it will move to A7. Now the result should be 'number of rows in-between' i.e 2 in this case. This result should be pasted in another column, lets say B1. This process need to be continued till my cursor reaches the B10 row. Please help.

Answer 1

This should do what you are after. It assumes that the data in column A is contiguous.

Sub helpSandeep()
    Dim c As Range
    Dim counting As Boolean
    Dim zeroCount As Long

    [B:B].ClearContents
    [B1] = "Results"

    For Each c In Sheets("Sheet1").[A1:A65536].Cells
        If Len(c) = 0 Then
            'Assuming that an empty cell means we are finished
            Exit For
        Else
            If c.Value2 = 1 Then
                'Update the results and reset the count
                counting = True 'Avoids counting any initial zeroes as per your example
                If zeroCount > 0 Then
                    [B65536].End(xlUp).Offset(1, 0) = zeroCount
                End If
                zeroCount = 0
            Else
                'Increment count, assuming we are actually counting
                If counting Then
                    zeroCount = zeroCount + 1
                End If
            End If
        End If
    Next c
End Sub

Answer 2

Enter with Control+Shift+Enter to make it an array formula. In B1

=LARGE((ROW($A$1:$A$10))*($A$1:$A$10=1),SUM(--($A$1:$A$10=1))-(ROW()))-LARGE((ROW($A$1:$A$10))*($A$1:$A$10=1),SUM(--($A$1:$A$10=1))-(ROW()-1))-1

and fill down. The formula consists of subtracting two LARGE functions and then subtracting 1 from the results.

The first argument to the LARGE functions is an array that returns the row number for every entry that contains a 1.

(ROW($A$1:$A$10))*($A$1:$A$10=1)

The second argument to the LARGE functions is a SUM array formula that counts all the entries with a 1, then subtracts the current row number (or the current row number -1). For a formula in B1, this would return 3-1 (or 2, the count of all entries with one (3) less the row number (1)).

For the second LARGE function, the second argument would return 3-0 (3).

All of that says, give me the 2nd largest row for the entries that contain a 1 and subtract the 3rd largest row for the entries that contain a 1.

When you fill down to B2, that becomes: give me the 1st largest row for the entries that contain a 1 and subtract the 2nd largest row.

Finally we subtract 1 from the whole thing to make the result exclusive (rather than inclusive) of the rows that contain the 1's.

I know you asked for a macro, but you may find formulas more suitable.