last non-empty cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

krayzie killa - 15 Mar 2008 13:53 GMT

is there a formula for displaying the last non-empty cell or the last used
cell in a column or row? if there is kindly notify me at
donald_dick13@yahoo.com. i needed it badly.

Reply to this Message

Don Guillett - 15 Mar 2008 14:05 GMT

one way is to look for a number or letter larger than possible
=match(99999999,a:a)
or
=match("zzzzzzzzz",a:a)

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> is there a formula for displaying the last non-empty cell or the last used
> cell in a column or row? if there is kindly notify me at
> donald_dick13@yahoo.com. i needed it badly.

Reply to this Message

Ron Rosenfeld - 15 Mar 2008 14:46 GMT

>is there a formula for displaying the last non-empty cell or the last used
>cell in a column or row? if there is kindly notify me at
>donald_dick13@yahoo.com. i needed it badly.

Last entry in column A:

=LOOKUP(2,1/(LEN(A:A)>0),A:A)

Last entry in Row 1:

=LOOKUP(2,1/(LEN(1:1)>0),1:1)

--ron

Reply to this Message

Teethless mama - 15 Mar 2008 16:29 GMT

Your formula returns #NUM! error prior to XL-2007

> Last entry in column A:
> =LOOKUP(2,1/(LEN(A:A)>0),A:A)

should be:
=LOOKUP(2,1/(LEN(A1:A65535)>0),A:A)

> >is there a formula for displaying the last non-empty cell or the last used
> >cell in a column or row? if there is kindly notify me at
[quoted text clipped - 9 lines]
>
> --ron

Reply to this Message

Ron Rosenfeld - 16 Mar 2008 01:04 GMT

>Your formula returns #NUM! error prior to XL-2007
>> Last entry in column A:
>> =LOOKUP(2,1/(LEN(A:A)>0),A:A)
>
>should be:
>=LOOKUP(2,1/(LEN(A1:A65535)>0),A:A)

I just switched to 2007. I guess "compatibility mode" in 2007 doesn't take
that into account. Thanks for the correction.
--ron

Reply to this Message