# How to get last non-empty cell in row(line)

Hello,
I have google and tried many possible way for couple hours now, but nothing works.
How to get last non-empty cell in row?

I have many numbers on one raw and then table which supposed to display the number in the last column. Like
1 2 3 4 5
So, in the sum table there is displayed 5.

1 2 3 4 5 6

The sum table will display 6.
So, I do not have to move the cell in sum table manualy to next column.
Im using LibreOffice 7.1.2.2

Thank you very much for advise.

P.S. and that â€śIâ€™m not a robotâ€ť cr*p drive mi crazy, took me 10 minutes to be those pictures clicable, not shady, click disapearing.

Maybe add a screenshot to make things more quickly understandable

Maybe Locating the final cell with data in a column will give you some hints.

Im already on column AE â€¦ so once I fill up the first empty field (gray row, after 0,43) the number will be displayed to the summary table. Till then the summary table will show 0,43.

And for 2nd row â€¦ summary table currently showing 0,54, so when I will enter new number next quarter, it will show the number entered to the AF column.

All formula I found is looking for last column, but I did not find how to adjust these formulas to look to specific row(line).
Like, Im looking for last number of row(line) 20, currently filled column AE, once next number filled, it will show column AF.

See this questionâ€™s answer: Return the last value in a column.

Thank you, but this is again about column, not row (line)> =LOOKUP(2;1/ISNUMBER(B27:B999);B27:B999) â€¦ it search column B from line 27 till 999.

But I need the opposite way â€¦ need search row(line) 27 (single row) from column B till column DZ

``=LOOKUP(2;1/ISNUMBER(B27:DZ27);B27:DZ27)``
1 Like

YES!!! This is working as I need.
Thank you very much. Realy appreciated.

If your data has numbers in 2nd row and will never have more than 26 (Z letter) columns, use smth like this (itâ€™s a trick that works for numbers):

=INDEX(\$A\$2:\$Z\$2;MATCH(9,99E+307;\$A\$2:\$Z\$2))

Ru Locale

Use comma instead of ; and point as decimal separator, if necessary.

9,99E+307 is the largest number. You can also use a smaller one. But it clearly identifies this trick.

BTW, if the data is string, then how? Replace the number with the Greek omega: â€śÎ©â€ť

It looks good, and is unlikely to be used by you, unless youâ€¦

It is never necessary. All locales accept the standard `;` argument separator; some also use other separators, like en-US, but they still accept `;` when typing (and will themselves â€śchangeâ€ť it to `,`).

OK. OP asked only for last number & I corrected the formula.

unfortunately not, Im already on column AE â€¦ see screen in above comment.

So what? What is the problem? Change the Z reference to the last column in the rowâ€¦

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