Ask Your Question

how to select last cell in column with data

asked 2020-07-04 12:27:20 +0200

zvone112 gravatar image

updated 2020-07-04 15:00:14 +0200

Hi I need to select and return text from last cell with text in column. Now i use =OFFSET(B3;COUNTIF(B3:B47;"<>")-1;0;1) but it work only if there are no empty cells between cells with text. Thanks in advance

edit retag flag offensive close merge delete


Yes. You surely expected tghis result.
A sometimes suggested replacement for the COUNTIF() part is
MAX((B3:B47)<>"")*ROW(B3:B47)). The overall expression must then be evaluated in iterative array-mode.

Lupp gravatar imageLupp ( 2020-07-04 14:21:53 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2020-07-04 15:48:02 +0200

zvone112 gravatar image

Data is text and not numbers. Search has to be performed in column b, from b3 to b47, the result (text from last cell with text in column) has to be shown in cell c48.

edit flag offensive delete link more



Lupp gravatar imageLupp ( 2020-07-04 16:09:05 +0200 )edit

answered 2020-07-04 15:30:18 +0200

Lupp gravatar image

The solution mentioned in my comment on the question has (at least) three disadvantages:
-1- The containing formula must be enterd for array-evaluation.
-2- The search range must be assured large enough. (Adaptions needed when scaling.)
-3- For large ranges it may be too inefficient.

If a user can accept to rely on user code ("macro"), it can be done rather efficient.
The attached demonstrating example contains a version of a related user function.
To avoid complications with the passing of a range as parameter, it relies on VBAsupport, and therefore it can only work in recent versions of LibreOffice having iplemented this support sufficiently.


edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-07-04 12:27:20 +0200

Seen: 43 times

Last updated: Jul 04