Since this kind of question is reoccurring, but solutions by formulas are sometimes rather “tricky” or may depend on rarely mentioned conditions like “last number” (or formula result or … what about …), “no gaps assured” and the like, “now first used cell in the column”, “want the address”, I also post a solution by user code I just sketched.
This shall not be misunderstood as a suggestion to rely on user code where clear solutions by standard functions are available.
REM ***** BASIC *****
Option VBAsupport 1
Function ultimateUsedCell(pRefCell , pDirection, pMode, pTypes)
REM Concerning pTypes see
REM https://api.libreoffice.org/docs/idl/ref/namespacecom_1_1sun_1_1star_1_1sheet_1_1CellFlags.html
Dim res
ultimateUsedCell = res
On Local Error Goto fail
rg = pRefCell.CellRange
If (rg.Columns.Count<>1) OR (rg.Rows.Count<>1) Then Goto fail
start = pRefCell.CellRange.getCellByPosition(0, 0)
Select Case pDirection
Case "r", 1, "l", 3 REM right or left (search row)
inRow = True
lookCells = start.Rows(0)
Case "d", 2, "u", 4 REM down or up (search column)
inRow = False
lookCells =start.Columns(0)
End Select
found = lookCells.queryContentCells(pTypes)
Select Case pDirection
Case "r", 1, "d", 2 REM right or down "last used cell"
final = found(found.Count-1)
ultimate = final.getCellByPosition(IIf(inRow, final.Columns.Count-1, 0), IIf(inRow, 0, final.Rows.Count-1))
Case "l", 3, "u", 4 REM left or up "first used cell"
final = found(0)
ultimate = final.getCellByPosition(0, 0)
End Select
Select Case pMode
Case "a" REM address (AbsoluteName)
res = ultimate.AbsoluteName
Case "v" REM content
res = ultimate.getDataArray()(0)(0)
End Select
ultimateUsedCell = res
fail:
End Sub
