Display the last cell used in another cell

[Solved/Closed]
Report
-
 Trowa -
Hello,
In a range of cells A1: A20 i want to show the data in the last cell used in cell A25. So it would look something loke tis

1
2
2
5





5
config>Windows XP / Internet Explorer 8.0</config>

4 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
What kind of data would be in your column

1. Numbers
2. text
3. alpha numeric
some thing else ?
Maybe I'm thinking to simple about this, but don't you just want to show the data in A20 (i.e. the last cell of range A1:A20) in A25.
Use =A20 in A25.
For some reason I can't find my latest post. Does Kioskea have some updating issues?

Anyway my last post for this query was rubbish anyway.

Now I'm wondering why Rizvisa wants to know what kind of data it is? Does it matter?

Assuming there are at least two entries in the range A1:A20 woudn't this code do the job:

Sub test()
Range("A1").End(xlDown).Copy
Range("A25").PasteSpecial
Application.CutCopyMode = False
End Sub
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Trowa, I was looking into use of VLOOKUP. As you are aware Trowa, the last parameter of VLOOKUP allows for deciding what kind of lookup needs to occur (exact = false) or (closest = true). Now if that is the case, you can use the most ridiculously long text of big number to find out the last used cell. But now that you have posed the question, it got me thinking and i think better approach would be to use

=INDIRECT("A" & COUNTA(A:A))
And of course only thing sure in this world of programming is that if there is a code, there is a way to break the dang thing. In this case, presence of a blank cell in Column A would kill the logic. Also a formula in the column would be counted as non-blank cell (which could be valid or invalid choice, depends on situation).

In case there is a blank cell, you can use this array formula. I know you know it Trowa, but for others, to enter a formula as array PRESS CTRL + SHIFT + ENTER as same time
=INDIRECT("A" & MAX(IF(A1:A65535<>"", ROW(A1:A
65535), 1)))

if all was done correctly, the formula should get enclosed in {}
Now the issue with this formula, what if data is on last row of sheet (65536).

Life is never simple eh ? :-)

I am sure there other ways too, but the best way, use a damn macro to locate the last used cell.
Nice to see you thinking out loud, Rizvisa. I just can't make your two functions work (they both result in a 0 when working with Rusty's example). But don't worry about me, let's hope Rusty found a suitable answer for his query.

Kind regards,
Trowa
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Pretty strange Trowa. I just copied the data and it was on range A1:A10 (5 -9 were blank)

For the CountA formula, I got a 0. It is giving 0 as count is saying that there are 5 rows and if you look at row 5, it is blank, so it is returning a 0 as formula do.

For the sumproduct one, I do get a 5 from the last row
Didn't give myself the time to think it through, I got them working now.

Catch you later Rizvisa.
Hi Everyone.
Thanks to all who answered me. I did get an answer and it works fine.
type in cell a25
=INDEX(A1:A20,COUNTA(A1:A20)
Again Thanks everyone