Find last non-blank cell in range
 

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

Thanks.

Find last non-blank cell in range
 

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



--
Gary''s Student - gsnu200753


"Eric" wrote:

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

Thanks.

Find last non-blank cell in range
 

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.


"Gary''s Student" wrote:

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



--
Gary''s Student - gsnu200753


"Eric" wrote:

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

Thanks.

Find last non-blank cell in range
 

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
--
Gary''s Student - gsnu200753


"Eric" wrote:

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.


"Gary''s Student" wrote:

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



--
Gary''s Student - gsnu200753


"Eric" wrote:

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

Thanks.

Find last non-blank cell in range
 

Thanks. Getting closer. I think this requires the values to be in ascending
order, doesn't it?

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200753


"Eric" wrote:

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.


"Gary''s Student" wrote:

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



--
Gary''s Student - gsnu200753


"Eric" wrote:

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

Thanks.

Find last non-blank cell in range
 

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
--
Gary''s Student - gsnu200753


"Eric" wrote:

Thanks. Getting closer. I think this requires the values to be in ascending
order, doesn't it?

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200753


"Eric" wrote:

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.


"Gary''s Student" wrote:

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



--
Gary''s Student - gsnu200753


"Eric" wrote:

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

Thanks.

Find last non-blank cell in range
 

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.

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200753


"Eric" wrote:

Thanks. Getting closer. I think this requires the values to be in ascending
order, doesn't it?

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200753


"Eric" wrote:

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.


"Gary''s Student" wrote:

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



--
Gary''s Student - gsnu200753


"Eric" wrote:

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

Thanks.

Find last non-blank cell in range
 

To return the last numerical value...

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

Hope this helps!

On Nov 1, 1:45 pm, Eric wrote:
How can I find the last (meaning bottom, or right-most) non-empty cell in a
range?

Thanks.

Find last non-blank cell in range
 

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

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

Find last non-blank cell in range
 

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

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.

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"&SUMPRODUC T(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

Find last non-blank cell in range
 

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.

"Rick Rothstein (MVP - VB)" wrote:

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

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.

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"&SUMPRODUC T(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

Find last non-blank cell in range
 

I know this is old but this did exactly what was asked (and what I wanted to
do) Thank you.

" wrote:

To return the last numerical value...

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

Hope this helps!

On Nov 1, 1:45 pm, Eric wrote:
How can I find the last (meaning bottom, or right-most) non-empty cell in a
range?

Thanks.