Consulting

Results 1 to 3 of 3

Thread: Count blank rows after non blank row

  1. #1

    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

  2. #2
    VBAX Regular rollis13's Avatar
    Joined
    Jun 2013
    Location
    Cordenons
    Posts
    93
    Location
    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

  3. #3
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,452
    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
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •