Results 1 to 4 of 4

Thread: Get last Filled Cell address in a Range.

  1. #1
    Member Rajan_Verma's Avatar
    Join Date
    Sep 2011
    Posts
    81
    Rep Power
    14

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

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    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?
    Last edited by Rick Rothstein; 03-24-2012 at 12:36 AM.

  3. #3
    Member Rajan_Verma's Avatar
    Join Date
    Sep 2011
    Posts
    81
    Rep Power
    14
    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)

  4. #4
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Rajan_Verma View Post
    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))))))

Similar Threads

  1. Replies: 15
    Last Post: 06-04-2013, 07:02 PM
  2. Replace Pivot Table Source Range with New Address VBA
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 1
    Last Post: 04-25-2013, 07:51 PM
  3. Replies: 1
    Last Post: 08-21-2012, 07:36 PM
  4. Last Filled Cell Having Text
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 5
    Last Post: 07-18-2011, 02:33 PM
  5. Replies: 6
    Last Post: 04-03-2011, 09:46 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •