| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find within range & Replace with Blank Value | Excel Discussion (Misc queries) | |||
| How do I find the next blank cell in a range? | Excel Worksheet Functions | |||
| Find First Non blank cell than find column header and return that value | Excel Worksheet Functions | |||
| find the first and last non blank cell in a row | Excel Discussion (Misc queries) | |||
| find the first blank cell in a range and return me it's position | Links and Linking in Excel | |||
Linear Mode
