[Solved] Find the contents of last used cell in a column?

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
imdumb
Posts: 29
Joined: Wed Oct 04, 2017 3:31 am

[Solved] Find the contents of last used cell in a column?

Post by imdumb »

I now have a number of long columns all of equal length, all of which, row by row, are getting steadily longer.

What I need to know is how to, at any given time, have a row that contains the contents of the final cell in each column that is not blank.

For example, say there are 26 columns of information (Columns A-Z), and each column has 100 cells containing information, and the final row that has any information is row 100. I'd need a row 26 cells (or columns) long, with each cell containing whatever row 100 has in it.

Then when row 101 is filled, the row would then have whatever row 101 had in it.

I couldn't find a function that could do this (but that doesn't necessarily mean there isn't one).

Know how to do this?

Thanks.
Last edited by imdumb on Sat Oct 14, 2017 7:29 pm, edited 1 time in total.
open office 4.1.2, windows 7
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: How do I find the contents of the last cell in a column?

Post by JohnSUN-Pensioner »

It took only nine years old, and again asked about it

Try just use GetLastUsedRow from standard library Tools - one of example of using - here (6 years ago)
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
imdumb
Posts: 29
Joined: Wed Oct 04, 2017 3:31 am

Re: How do I find the contents of the last cell in a column?

Post by imdumb »

Thanks for your efforts, unfortunately I couldn't make your suggestion work.

That certainly doesn't mean you gave advice that doesn't work.
A better explanation? Well, note my name :D

But here's a different solution:

Find the last number used in column A:

............A
1...........1
2...........2
3...........3
4...........=INDEX(A1:A3;COUNTA(A1:A3))

It gives a "3". Just what I need!
open office 4.1.2, windows 7
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: (SOLVED) Find the contents of last used cell in a column

Post by Zizi64 »

imdumb,
your solution works well, when your data is continous, without empty cells inside the used cellrange.

The value of the last used cell on a column and the value indexed to the count of the data values maybe will be in different place.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: (SOLVED) Find the contents of last used cell in a column

Post by Villeroy »

=MAX(ISNUMBER(A1:A1048576)*ROW(A1:A1048576)) [Ctrl+Shift+Enter]
=MAX(ISTEXT(A1:A1048576)*ROW(A1:A1048576)) [Ctrl+Shift+Enter]
=MAX(NOT(ISBLANK(A1:A1048576))*ROW(A1:A1048576)) [Ctrl+Shift+Enter]
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply