Find last non-blank cell in range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How can I find the last (meaning bottom, or right-most) non-empty cell in a
range?

Thanks.
 
Select the range and:

Sub findit()
For Each r In Selection
If IsEmpty(r) Then
Else
s = r.Address
End If
Next
MsgBox (s)
End Sub
 
Thanks for the suggestion.

I'll use a macro if I have to, but what I was hoping to find is a function,
or a series of functions. For example, LAST_NONEMPTY_CELL_VAL(A:A) = 12345,
or LAST_NONEMPTY_CELL_ADDR(A:A) = "A124". Something like that.
 
From your example (column A):

=ADDRESS(MATCH(9.99999999999999E+307,A:A),1) will return the address of the
last used cell IF IT IS A NUMBER

Hence if the above formula is in B1, then:
=INDIRECT(B1) will return the value in that last used cell
 
Thanks. Getting closer. I think this requires the values to be in ascending
order, doesn't it?
 
Nope. I entered data:

in A1: 1
in A2: 2
in A3:
in A4: 4
in A5:
in A6:
in A7: 6
in A8:
in A9: -10

The formula returns: $A$9
 
Here's what the Excel help text says for Match: "If match_type is 1, MATCH
finds the largest value that is less than or equal to lookup_value.
Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z,
FALSE, TRUE."

But you're right, it works, though I'm not sure why.
 
To return the last numerical value...

=LOOKUP(9.99999999999999E+307,A2:A100)

Hope this helps!
 
How can I find the last (meaning bottom, or right-most)
non-empty cell in a range?
Click to expand...

I think these should work for you....

Last Used Column
***********************
=SUMPRODUCT(MAX((COLUMN(A1:H17)*(A1:H17<>""))))

Last Used Row
***********************
=SUMPRODUCT(MAX((ROW(A1:H17)*(A1:H17<>""))))

Change the ranges to suit your needs.

Rick
 
How can I find the last (meaning bottom, or right-most)
I think these should work for you....

Last Used Column
***********************
=SUMPRODUCT(MAX((COLUMN(A1:H17)*(A1:H17<>""))))

Last Used Row
***********************
=SUMPRODUCT(MAX((ROW(A1:H17)*(A1:H17<>""))))

Change the ranges to suit your needs.
Click to expand...

I think I misread what you wanted. I think the following does what you
want...

Last Used Column
**************************************************
=SUMPRODUCT(MAX((COLUMN(A1:H17)*(A1:H17<>""))))

Last Non-Used Cell in Last Used Column
**************************************************
=1+SUMPRODUCT(MAX((COLUMN(INDIRECT("A"&SUMPRODUCT(MAX((ROW(A1:H17)*(A1:H17<>""))))&":"&"H"&SUMPRODUCT(MAX((ROW(A1:H17)*(A1:H17<>""))))))*(INDIRECT("A"&SUMPRODUCT(MAX((ROW(A1:H17)*(A1:H17<>""))))&":"&"H"&SUMPRODUCT(MAX((ROW(A1:H17)*(A1:H17<>"")))))<>""))))

I'm pretty sure there are more efficient formulas to be had. Anyway, while
I'm looking for them, you can use the above in the meantime...

Rick
 
Thanks! That formula is a lesson in itself. It will take me a while to
figure it out, but I'm sure I'll learn a lot in the process.
 
I know this is old but this did exactly what was asked (and what I wanted to
do) Thank you.
 
First, I like to use the the best practies that Charley Kyd
(http://www.exceluser.com/) prescribes for ranges - add a blank colored cell
at each end of the range and name the range. Insertions at the beginning or
end of the range but within the colored boundaries will cause the named range
to always refer the content within the boundaries.

If your range is vertical and called "names", the following will return the
last non-blank entry in the range.

={INDEX(names,MAX(IF(names>"",1,0)*(ROW(names)-ROW(OFFSET(names,0,0,1,1)))+1,1))}
 
Back
Top