consider last row if there is any blank cell in between

elsuji · Sep 8th 2021

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

rollis13 · Sep 8th 2021

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

elsuji · Sep 8th 2021

Thanks for your reply

jolivanes · Sep 9th 2021

Another way.

Code

Cells(Cells(1).CurrentRegion.Rows.Count + 1, 1).Select

Or so

Code

ActiveSheet.UsedRange.Columns(1).SpecialCells(4).Cells(1).Select

consider last row if there is any blank cell in between

Participate now!

Share