Printable View
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?
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?Quote:
Originally Posted by xld
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.
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.
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.
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!
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.
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]Quote:
Originally Posted by xld