Count blank rows after non blank row

[talytech]

Hello
I am trying to determine how many blank rows until the next non blank row in column A (ID).

In the table below, I would like to do something like a For Next Loop and capture the total blank rows after the first ID.

So: ID 12345 = 2 empty/blank rows.

ID	Value
12345
24566
333222

[rollis13]

As per your example (with borders), supposing that your first title is in cell A1 and that the sheet has no other complied or used cells outside your table have a try with my macro to be copied in a standard vbe module:

Option Explicit
Sub Count_Blank()
    Dim x, lr, rw, cnt
    Dim flag   As Boolean
    lr = ActiveSheet.UsedRange.Rows.Count - 1
    For x = 2 To lr                               '<- adjust if titles aren't in row 1
        If Range("A" & x) <> "" Then
            If flag = False Then
                rw = x
                flag = True
            Else
                Range("B" & rw) = cnt             '<- reports number of blank lines
                cnt = 0
                flag = True
                rw = x
            End If
        Else
            cnt = cnt + 1
        End If
    Next x
    If x > rw Then Range("B" & rw) = cnt          '<- reports number of blank lines for last ID
End Sub

[p45cal]

In B2, copied down a formula such as:
=IF(LEN(A2)>0,MATCH(TRUE,LEN(A3:A$10)>0,0)-1,"")

or a macro:

Sub blah()
For Each are In ActiveSheet.UsedRange.Columns(1).SpecialCells(xlCellTypeBlanks).Areas
  are.Cells(1).Offset(-1, 1).Value = are.Cells.Count
Next are
End Sub

or because the above only updates certain cells you can clear that column first:

Sub blah()
With ActiveSheet.UsedRange
  .Columns(2).Clear
  For Each are In .Columns(1).SpecialCells(xlCellTypeBlanks).Areas
    are.Cells(1).Offset(-1, 1).Value = are.Cells.Count
  Next are
End With
End Sub