Get last Filled Cell address in a Range.

[Rajan_Verma]

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



=ADDRESS(MAX(IF(LEN(A1:I36)>0,ROW(A1:I36))),MAX(IF(LEN(A1:I36)>0,COLUMN(A1:I36))))

[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?

[Rajan_Verma]

ok,
Try this one.


=IFERROR(ADDRESS(MAX(IF(LEN(A1:I36)>0,ROW(A1:I36)),0),MAX(IF(LEN(INDIRECT(MAX(IF(LEN(A 1: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)

[Rick Rothstein]

ok,
Try this one.


=IFERROR(ADDRESS(MAX(IF(LEN(A1:I36)>0,ROW(A1:I36)),0),MAX(IF(LEN(INDIRECT(MAX(IF(LEN(A 1: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...


=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))))))