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