you can use the SpecialCells funciton passing the Last used cell constant (xlCellTypeLastCell) or pass xlCellTypeBlanks if you only want to look for a blank cell


Code:
Dim oRange As Range
Set oRange = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)
MsgBox oRange.Address