Count blank rows after non blank row

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

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:

Code:

---

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

---

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

or a macro:

Code:

---

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:

Code:

---

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

---