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

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.

When I add next number>
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

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.

Table looks like this:
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

Just adapt the references:

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):


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…

Use comma instead of ;

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 
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
End Sub

Im truly sorry, but where do I supposed to put this?