last non-empty cells

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.

Don
Sat Mar 15 08:05:09 CDT 2008


one way is to look for a number or letter larger than possible
=match(99999999,a:a)
or
=match("zzzzzzzzz",a:a)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"krayzie killa" <krayzie killa@discussions.microsoft.com> wrote in message
news:FF7B0D9F-2E8C-4057-966F-CE0660E4041A@microsoft.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.

Ron
Sat Mar 15 08:46:54 CDT 2008

On Sat, 15 Mar 2008 05:53:00 -0700, krayzie killa <krayzie
killa@discussions.microsoft.com> wrote:

>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

Teethlessmama
Sat Mar 15 10:29:02 CDT 2008

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)


"Ron Rosenfeld" wrote:

> On Sat, 15 Mar 2008 05:53:00 -0700, krayzie killa <krayzie
> killa@discussions.microsoft.com> wrote:
>
> >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
>

Ron
Sat Mar 15 19:04:01 CDT 2008

On Sat, 15 Mar 2008 08:29:02 -0700, Teethless mama
<Teethlessmama@discussions.microsoft.com> wrote:

>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