Address of Last Cell With Data
 

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.
--
tb

Address of Last Cell With Data
 

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)

"tb" wrote in message
...
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.
--
tb

Address of Last Cell With Data
 

=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

On Wed, 12 Dec 2007 16:42:43 -0800 (PST), tb wrote:

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 of Last Cell With Data
 

On Wed, 12 Dec 2007 16:42:43 -0800 (PST), tb wrote:

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

Address of Last Cell With Data
 

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)

"Ron Coderre" wrote in message
...
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)

"tb" wrote in message
...
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.
--
tb