Related:

- Display the last cell used in another cell
- Excel - Display Formula in Cell - How-To - Excel
- Display value if cell contains text ✓ - Forum - Excel
- Excel display text if cell contains ✓ - Forum - Excel
- How to display warning/alert messages if cells are blank in excel vba ✓ - Forum - Excel
- Display message box based on cell value ✓ - Forum - Excel

## 4 replies

rizvisa1

- Posts
- 4476
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- August 2, 2020

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 ?

Trowa

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.

Trowa

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
- 4476
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- August 2, 2020

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
- 4476
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- August 2, 2020

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