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