Results 1 to 4 of 4

Thread: VBA code to count cells to down until first non blank

  1. #1
    New Member
    Join Date
    Aug 2017
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA code to count cells to down until first non blank

    Hi All,

    I need a code that count the cells all the way down until the first non blank cell.

    Activecell
    Blank
    Blank
    Blank
    NonBlank

    So in this way I need 3+1 that is 4

    I need this with active cell so I can refer this number in the code later like

    X= VBAcode (i got the number here)

    and later

    If X = 4 then and so on

    Can somebody help me with this?

    Thank you in Advance,

    Philip

  2. #2
    Board Regular gallen's Avatar
    Join Date
    Jun 2011
    Location
    Manchester UK
    Posts
    1,895
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA code to count cells to down until first non blank

    Does this help?

    Code:
    Function RowsToNextNonBlank() As Long
        Dim l As Long 'counter
        
        'initialise counter
        l = 1
        'Find next cell with a value. Stop after 1000 tries to prevent over long loop
        Do Until ActiveCell.Offset(l).Value <> ""
            l = l + 1
            'if over 1000 assume no more values
            If l > 1000 Then
                RowsToNextNonBlank = 0
                MsgBox "Next none-blank cell not found"
                Exit Function
            End If
        Loop
        
        'return the number stored in 'l'
        RowsToNextNonBlank = l
    
    
    End Function
    
    
    Sub Test()
        Dim x As Long
        
        x = RowsToNextNonBlank
        MsgBox x
    End Sub

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


  3. #3
    New Member
    Join Date
    Aug 2017
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code to count cells to down until first non blank

    Quote Originally Posted by gallen View Post
    Does this help?

    Code:
    Function RowsToNextNonBlank() As Long
        Dim l As Long 'counter
        
        'initialise counter
        l = 1
        'Find next cell with a value. Stop after 1000 tries to prevent over long loop
        Do Until ActiveCell.Offset(l).Value <> ""
            l = l + 1
            'if over 1000 assume no more values
            If l > 1000 Then
                RowsToNextNonBlank = 0
                MsgBox "Next none-blank cell not found"
                Exit Function
            End If
        Loop
        
        'return the number stored in 'l'
        RowsToNextNonBlank = l
    
    
    End Function
    
    
    Sub Test()
        Dim x As Long
        
        x = RowsToNextNonBlank
        MsgBox x
    End Sub
    Yes! Exactly that is needed for me. Thank you so much!!!

  4. #4
    Board Regular gallen's Avatar
    Join Date
    Jun 2011
    Location
    Manchester UK
    Posts
    1,895
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA code to count cells to down until first non blank

    No worries. Happy it helps.

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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