consider last row if there is any blank cell in between

  • Dear Team,


    In column A, if there is any blank cell, I won't use the last row till the blank cell. Not the entire column last used cell.


    For example,


    My range starts from A9. My values are entered A9 to A40. But If suppose A30 is a blank cell. So my last row should work till A29. Not till A40. Can anyone help me how to do this

  • A snippet to detect the first empty cell, could be:

    Code
    Option Explicit
    Sub FirstEmptyCell()
        Dim fec    As Long
        With ActiveSheet
            fec = .Range("A8").End(xlDown).Row + 1
            If .Range("A9") = "" Then fec = 9
        End With
        MsgBox fec      'only for testing
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!