last non-empty cells

K

krayzie killa

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
[email protected]. i needed it badly.
 
D

Don Guillett

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

Ron Rosenfeld

On Sat, 15 Mar 2008 05:53:00 -0700, krayzie killa <krayzie
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
[email protected]. i needed it badly.
Click to expand...

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
 
T

Teethless mama

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

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

Ron Rosenfeld

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

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

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