Get last Filled Cell address in a Range.

Printable View

03-23-2012, 11:14 PM
Rajan_Verma

Get last Filled Cell address in a Range.

Array Formula to get last filled cell address in a given range

Code:

=ADDRESS(MAX(IF(LEN(A1:I36)>0,ROW(A1:I36))),MAX(IF(LEN(A1:I36)>0,COLUMN(A1:I36))))
03-24-2012, 12:09 AM
Rick Rothstein

Assume there are values in A1, A2, A3, B1 and C1... which is the last filled cell? Your formula says C3 but I can make a strong case (at least in my own mind) for A3 being the better answer (last filled cell on the last row of data). Your thoughts?
03-24-2012, 12:13 AM
Rajan_Verma

ok,
Try this one.

Code:

=IFERROR(ADDRESS(MAX(IF(LEN(A1:I36)>0,ROW(A1:I36)),0),MAX(IF(LEN(INDIRECT(MAX(IF(LEN(A1:I36)>0,ROW(A1:I36)),0)&":"&MAX(IF(LEN(A1:I36)>0,ROW(A1:I36)),0)))>0,COLUMN(INDIRECT(MAX(IF(LEN(A1:I36)>0,ROW(A1:I36)),0)&":"&MAX(IF(LEN(A1:I36),ROW(A1:I36)),0)))))),0)
03-24-2012, 01:08 AM
Rick Rothstein

Quote:

Originally Posted by Rajan_Verma

ok,
Try this one.

Code:

=IFERROR(ADDRESS(MAX(IF(LEN(A1:I36)>0,ROW(A1:I36)),0),MAX(IF(LEN(INDIRECT(MAX(IF(LEN(A1:I36)>0,ROW(A1:I36)),0)&":"&MAX(IF(LEN(A1:I36)>0,ROW(A1:I36)),0)))>0,COLUMN(INDIRECT(MAX(IF(LEN(A1:I36)>0,ROW(A1:I36)),0)&":"&MAX(IF(LEN(A1:I36),ROW(A1:I36)),0)))))),0)

That code works in XL2007 but fails in XL2003 (I'm guessing there are too many nested levels). This shorter array-entered formula seems to work in both Excel versions though...

Code:

=ADDRESS(MAX((A1:I36<>"")*ROW(A1:I36)),MAX((INDIRECT("A"&MAX((A1:I36<>"")*ROW(A1:I36))&":I"&MAX((A1:I36<>"")*ROW(A1:I36)))<>"")*COLUMN(INDIRECT("A"&MAX((A1:I36<>"")*ROW(A1:I36))&":I"&MAX((A1:I36<>"")*ROW(A1:I36))))))