Using Formula To Find Last Used Column?

[mikerickson]

When looking for the last used column in A2:A10 as a test range,
I got good numbers by selecting a column (A2:A20) and entering the array formula
{=MATCH(9E+25,OFFSET($A$2,{0;1;2;3;4;5;6;7;8;9},0,1,256))}

And =MAX(A12:A20) returned the column number (if there were no #NA)

But the array formula {=MAX(MATCH(9E+25,OFFSET($A$2,{0;1;2;3;4;5;6;7;8;9},0,1,256)))} returned #NA.

Using Named values produced a work-around.

Name: LC2 RefersTo: =IF(ISNUMBER(MATCH(9E+25,Sheet1!$2:$2)),MATCH(9E+25,Sheet1!$2:$2),0)
Name: LC3 RefersTo: =IF(ISNUMBER(MATCH(9E+25,Sheet1!$3:$3)),MATCH(9E+25,Sheet1!$3:$3),0)
...
Name: LC10 RefersTo: =IF(ISNUMBER(MATCH(9E+25,Sheet1!$10:$10)),MATCH(9E+25,Sheet1!$10:$10),0)

and
Name: LastFilledColumn RefersTo: = MAX(LC2,LC3,...,LC9,LC10)

The name LastFilledColumn returns the desired result. (The column number of the right-most numeric cell.)

This sub will create the names, saving a ton of typing after topRowNumber and bottomRowNumber are adjusted. (note: top < bottom)
[VBA]Sub MakeNames()
Dim topRowNum As Long
Dim bottomRowNum As Long
Dim RefersToString As String, NameString As String
Dim StringForMax As String
Dim i As Long

topRowNum = 2
bottomRowNum = 10

For i = topRowNum To bottomRowNum
NameString = "LC" & i
RefersToString = "MATCH(9E+25,R" & i & ")"
RefersToString = "=IF(ISNUMBER(" & RefersToString & ")," & RefersToString & ",0)"
StringForMax = StringForMax & "," & NameString
ThisWorkbook.Names.Add Name:=NameString, RefersToR1C1:=RefersToString
Next i
ThisWorkbook.Names.Add Name:="LastFilledCol", RefersTo:="=MAX(" & Mid(StringForMax, 2) & ")"
End Sub
[/VBA]I hope this helps.

A problem with using OFFSET in an array formula is that the range in OFFSET(A2:A10,0,0,1,256) is read as a single, constant argument rather than separate arguments for each element of the desired array. Similarly ROW,ROWS and any other function that takes a multi-cell range as an argument.