Using Formula To Find Last Used Column?

[Simon Lloyd]

Using =ADDRESS(MATCH(1E+30,A:A),COLUMN(A1)) i can get the last used cell in column A and using ="$A$2"&":"&ADDRESS(MATCH(1E+30,A:A),COLUMN(A1)) i can get the used range of column A but i am having a great deal of trouble manipulating a formula to show the last column that has data in it excluding the header!

Does anyone have an idea on a formula to return the address of the last used coulmn that has data in it and maybe even the cell address in that column?

[Bob Phillips]

ARe you looking for something that will find say M17 where you have data in A7, B8 and M17, and headings in A1:P1

[Simon Lloyd]

ARe you looking for something that will find say M17 where you have data in A7, B8 and M17, and headings in A1:P1

Yes Bob, exactly that! is it possible? what am i saying?, the fact that you answered and asked that question says it is!, if a cell has a formula but not returning a value so effectively blank would it return that or ignore it?

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

[Simon Lloyd]

Mike thanks for the reply, i only want a formula version if possible and i would further use that inside another formula, however i tried your suggestions but didn't produce the expect results, if i have data in lets say column E (excluding header) row 5 then the formula no matter where located should give the result $E$5, the formula you provide gave the result 4 when i had used 5 columns. I really would like to refrain from using a UDF.

[Bob Phillips]

Simon,

I think a UDF is the way to go here, a formula would be horrendous.

I did knock one up the other day, and have lost it. If you are interested, I will re-create.

[Simon Lloyd]

Bob, i would like that, purely because i can already create 70% of everything i would ever be likely to do in VBA fairly easily, lately i have been making a conscious effort try to work with excel' own built in functionality, formula to me are for the most part already horrendous but i think a workbook travels better with formula rather than VBA, lately i've been reading J WalkenBach' book on formula just a few pages in and you feel over your head but i'm getting there!

[mikerickson]

The VB only writes names at design time. It doesn't execute at run time.

I ran MakeNames, removed the sub, entered a number in E5 and the formula
=LastFilledCol in B1, which returned 5.

One drawback to this approach is the need for a "helper" name for each row of the data range.

[Bob Phillips]

I meant I created a UDF, not a formula solution.

[Simon Lloyd]

I meant I created a UDF, not a formula solution.

Ok, in that case if we're using a UDF we can check HasFormula to ignore those cells that have a formula but return no result, right? e.g something like [VBA]If Range("A1").HasFormula and Range("A1").Value="" Then....[/VBA]