Address of Last Cell With Data

[tb]

Is it possible to detect the address of the last cell with data in a
worksheet? (Without having to resort to VBA!)
For instance, if I have data in column A, what formula would I use to
detect the address of the last cell with data?
Thanks.

 

[Ron Coderre]

Try this:
=CELL("address",INDEX(A1:A65535,MATCH(2,INDEX(1/(1-ISBLANK(A1:A65535)),0))))


Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Gord Dibben]

=LOOKUP(2,1/(A1:A65535<>""),A1:A65535) for any data

=ADDRESS(MATCH(99^99,A:A),1) for numeric data

=LOOKUP(REPT("z",255),A:A) for text data


Gord Dibben MS Excel MVP

 

[Ron Rosenfeld]

Is it possible to detect the address of the last cell with data in a
worksheet? (Without having to resort to VBA!)
For instance, if I have data in column A, what formula would I use to
detect the address of the last cell with data?
Thanks.

=ADDRESS(MATCH(2,1/NOT(ISBLANK(A1:A65535))),1)

entered as an **array** formula (enter with <ctrl><shift><enter>)


--ron

 

[Ron Coderre]

I should mention that the formula I posted will return
the cell address of the last non-blank cell (text OR numeric).

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)