Display the last cell used in another cell
Solved/Closed
Related:
- Display the last cell used in another cell
- Clear only the formatting from the selected cell (leaving the content) - Guide
- Count if cell contains number - Excel Forum
- The page cannot be displayed. (80710a06) ✓ - Network Forum
- Based on the cell values in cells b77 - Excel Forum
- To display a text file in reverse order what command should be used - Guide
4 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jul 22, 2010 at 03:03 PM
Jul 22, 2010 at 03:03 PM
What kind of data would be in your column
1. Numbers
2. text
3. alpha numeric
some thing else ?
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.
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
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
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jul 23, 2010 at 12:01 PM
Jul 23, 2010 at 12:01 PM
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.
=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.
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jul 27, 2010 at 09:35 AM
Jul 27, 2010 at 09:35 AM
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
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
