how to copy the contents from last cell
 

I have a column of data but I do not know how many cells in column. I want
to copy the last cell from this column. e.g.

Example 1
========
A1 = 412
A2 = 343
A3 = 434
A4 = <empty
A5 = 3432
A6 = 34
A7 = 3431
A8=<empty
A9=<empty
.....
and so on cells are empty
....

....

I want B1 = 3431 (A7) because it is the last cell with non empty content


Example 2
========
A1 = 412
A2 = 343
A3 = 434
A4 = <empty
A5 = 3432
A6 = 34
A7 = 3431
A8=123
A9=456
A10=<empty
A11=<empty
A12=<empty
---
and so on cell s are empty...

....

I want B1 = 456 (A9) because it is the last cell with non empty content


Thanks in advance.

how to copy the contents from last cell
 

On Jan 16, 5:20*pm, "Lamb Chop" wrote:
I have a column of data but I do not know how many cells in column. *I want
to copy the last cell from this column. e.g.

Example 1
========
A1 = 412
A2 = 343
A3 = 434
A4 = <empty
A5 = 3432
A6 = 34
A7 = 3431
A8=<empty
A9=<empty
....
and so on cells are empty
...

...

I want B1 = 3431 *(A7) *because it is the last cell with non empty content

Example 2
========
A1 = 412
A2 = 343
A3 = 434
A4 = <empty
A5 = 3432
A6 = 34
A7 = 3431
A8=123
A9=456
A10=<empty
A11=<empty
A12=<empty
---
and so on cell s are empty...

...

I want B1 = 456 *(A9) *because it is the last cell with non empty content

Thanks in advance.

Add this in a module in VBE, it'll do what your looking for

Sub Try()
Range("B1").Value = Range("A65536").End(xlUp).Value

End Sub

how to copy the contents from last cell
 

In B1 enter this formula.

=LOOKUP(99^99,A:A) to return last number

=LOOKUP(2,1/(A1:A65535<""),A1:A65535) to return number or text.


Gord Dibben MS Excel MVP

On Thu, 17 Jan 2008 10:20:31 +1100, "Lamb Chop" wrote:

I have a column of data but I do not know how many cells in column. I want
to copy the last cell from this column. e.g.

Example 1
========
A1 = 412
A2 = 343
A3 = 434
A4 = <empty
A5 = 3432
A6 = 34
A7 = 3431
A8=<empty
A9=<empty
....
and so on cells are empty
...

...

I want B1 = 3431 (A7) because it is the last cell with non empty content


Example 2
========
A1 = 412
A2 = 343
A3 = 434
A4 = <empty
A5 = 3432
A6 = 34
A7 = 3431
A8=123
A9=456
A10=<empty
A11=<empty
A12=<empty
---
and so on cell s are empty...

...

I want B1 = 456 (A9) because it is the last cell with non empty content


Thanks in advance.