VBA code to count cells to down until first non blank

[Philip00]

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

 

[gallen]

Does this help?

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

 

[Philip00]

Does this help?

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!!!

 

[gallen]

No worries. Happy it helps.