Howto Find LAST empty CELL in RANGE

[Webtest]

This is the basic idea. If you have multiple Workbooks open or multiple sheets, you'll need to handle those specifically. This works for one sheet in one book.

Option Explicit

Sub Macro1()
    Dim aRow As Long
    Dim aRange As Range
    
    'This is an attempt to find a cell in column A that is guaranteed to be empty
    'Find the row number below the bottom-most occupied cell
    aRow = Sheets(1).Cells.SpecialCells(xlCellTypeLastCell).Row + 1

    While Cells(aRow, "A").Value = ""
        aRow = aRow - 1
    Wend
    
    'aRange is the first available empty cell below the last used cell in Column A
    Set aRange = Cells(aRow + 1, "A")
    
    MsgBox aRange.Address

End Sub