I am looking for code that will find the last used column. In other words, the last column (left - to - right) that has a value in it.
Thanks.
Picco
I am looking for code that will find the last used column. In other words, the last column (left - to - right) that has a value in it.
Thanks.
Picco
You can do it with a worksheet function. This is for row 3Originally Posted by crmpicco
and is an array formula, so commit with Ctrl-Shift-Enter=MAX(IF(3:3<>"",COLUMN(3:3)))
sorry, how do you mean a worksheet function. i kind of need to this automatically for each sheet. any ideas?
I mean enter the formula in a cell in the sheet. You can enter it in each sheet, it will apply to that.Originally Posted by crmpicco
I enteredinto cell A2 which is empty and the cell value changed to '0'.=MAX(IF(3:3<>"",COLUMN(3:3)))
What is that calculating???
thanx
I assume that row 3 has some values. Did you array enter it, commit with Ctrl-Shift-Enter?Originally Posted by crmpicco
No, row 2, row 3 and row 4 are completely empty.
When you say array enter i'm not totally sure what you mean. I need this to be an automatic process with no manual intervention.
Is there a way i can do this?
Thanks for your help so far....
If row 3 is empty, the last column is 0, so it is correctOriginally Posted by crmpicco
When you enter a formula, normally you type it in, then hit the Enter key. With an array formula, you type it in, then hit Ctrl-Shift-Enter together. You will then see the formula in the formula bar surrounded by braces {=formula}. Those braces are automatic, you do not type them, or change them, except by the Ctrl-Shift-Enter.Originally Posted by crmpicco
By virtue of being a worksheet formula, every time something in the target range changes, the formula will update.Originally Posted by crmpicco
Hi crmpicco,
If you want a VBA solution, try this
HTH,Sub LastOne() Dim Last As Range Set Last = [A1].SpecialCells(xlCellTypeLastCell) MsgBox "Column " & Last.Column & " is the last column" '< msgbox example Columns(Last.Column).Select '< select example End Sub
John
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
The last column overall or just for the current row?
Take this example:
Row 1: Data in Column 1 - 3
Row 2: Data in Column 1 - 5
If you only look at row1, should it give you 3 or 5 ?
that seems to be the same as:Sub LastOne() Dim Last As Range Set Last = [A1].SpecialCells(xlCellTypeLastCell) MsgBox "Column " & Last.Column & " is the last column" '< msgbox example Columns(Last.Column).Select '< select example End Sub
ActiveSheet.UsedRange.Rows.Count
Hi crmpicco,
Yes, it may seem the same but they give subtly different results related to whether there are any hidden or unhidden columns.
I just re-read your original question more carefully and I see now that you want the last column with a Value in it. Both the above will usually do that, but they can both fail if there are any formatted (borders, coloured, etc.) cells past the last Value(s).
The most reliable way to obtain the last column of values is to use the Find function E.G.
HTH,Option Explicit Sub Last_Find() Dim Last As Long Last = Cells.Find("*", SearchOrder:=xlByColumns, _ LookIn:=xlValues, SearchDirection:=xlPrevious).Column MsgBox Last End Sub
John
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
that works great - thanks again.
Not a prob., glad to be able to help
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.