Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Return the last column address (Excel 2003)

    What formula will give a result of column address which reflects the last nonempty column used in the worksheet?

    eg. if there is
    something in C1, H1, and M1, and
    something in F3, and
    something in AA6

    Thanks

    Regards, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Return the last column address (Excel 2003)

    You could use this custom VBA function:

    Function LastColumn(oCell As Range)
    LastColumn = oCell.Parent.Cells.Find(What:="*", SearchOrder:=xlByColumns, _
    SearchDirection:=xlPrevious).Column
    End Function

    In a cell formula:

    =LastColumn(A1)

    or if you place the function in your Normal.dot:

    =Personal.xls!LastColumn(A1)

    You can also retrieve the last used column in another sheet, if desired:

    =LastColumn(Sheet3!A1)

  3. #3
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans,
    I am trying to adapt this custom function to a project I am working on. I need to determine the last column used within columns X:AZ for each row of a spreadsheet and then return the value of row 1 for that column into a cell. It seems as if this function is looking at the entire worksheet even if I enter a range in the formula. Any suggestions? Would another formula work better?

    I have attached a sample workbook. The column I am looking to populate is W.
    Thanks!
    Greg[attachment=86684:WOPR_WSU.xlsx]
    Attached Files Attached Files
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The function that I posted indeed looks at the entire worksheet; this was by intent.

    You should use a different formula: in W3, enter this formula:

    =INDEX($X$1:$AZ$1,MATCH(9.99999999999999E+307,$X3: $AZ3))

    If necessary, format the cell as a date, and fill down as far as needed. The formula will return #N/A if no payments have been made, which is not unreasonable. If you'd like to suppress the error value, you can use

    =IF(ISNA(MATCH(9.99999999999999E+307,$X3:$AZ3)),"" ,INDEX($X$1:$AZ$1,MATCH(9.99999999999999E+307,$X3: $AZ3)))

  5. #5
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Perfect Hans!
    Thank You
    Greg
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

Posting Permissions

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