Return the last column address (Excel 2003)

Topic

New Reply

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

Reply | Quote

Replies

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)

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Perfect Hans!
Thank You
Greg

Reply | Quote