Finding Last Data in a Column

  • Thread starter Thread starter MacroMike
  • Start date Start date
M

MacroMike

Hi,
Does anyone kow how to find the last entry in a single column the
paste it into another cell?

For instance - Looking at column J - Find the last entry or last cel
with data in it then copy this data into another cell eg J5.


Any help much appreciated


Mike:) :) :
 
Hi Mike
WorkSheets("Sheet1").Range("J65536").End(xlUp).Copy
WorkSheets("Sheet1").Range("J5")

HTH
Cordially
Pascal
 
Please note code should be in one whole line.

Cordially
Pascal
papou said:
Hi Mike
WorkSheets("Sheet1").Range("J65536").End(xlUp).Copy
WorkSheets("Sheet1").Range("J5")

HTH
Cordially
Pascal
Click to expand...
 
Hi,
Does anyone kow how to find the last entry in a single column then
paste it into another cell?

For instance - Looking at column J - Find the last entry or last cell
with data in it then copy this data into another cell eg J5.


Any help much appreciated


Mike:) :) :)
Click to expand...

It depends on the type of data for which you are looking:

For example, if the data is a number, then the **array** formula:

=INDEX(A1:A65535,MAX(ISNUMBER(A1:A65535)*ROW(INDIRECT("1:65535"))))

will give the value of that entry.

To enter an **array** formula, hold down <ctrl><shift> while hitting <enter>.
XL will place braces {...} around the formula.

If it could be either a number or text, then the **array** formula:

=INDEX(A1:A65535,MAX((ISNUMBER(A1:A65535)+ISTEXT(A1:A65535))*ROW(INDIRECT("1:65535"))))

should do the job.

If there are formulas in the column that may return a null string, the formula
would have to be modified.


--ron
 
Back
Top