Find the last used column

[crmpicco]

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

[Bob Phillips]

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.

You can do it with a worksheet function. This is for row 3

=MAX(IF(3:3<>"",COLUMN(3:3)))

and is an array formula, so commit with Ctrl-Shift-Enter

[crmpicco]

sorry, how do you mean a worksheet function. i kind of need to this automatically for each sheet. any ideas?

[Bob Phillips]

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.

[crmpicco]

I entered

 =MAX(IF(3:3<>"",COLUMN(3:3)))

into cell A2 which is empty and the cell value changed to '0'.

What is that calculating???

thanx

[Bob Phillips]

I entered

 =MAX(IF(3:3<>"",COLUMN(3:3)))

into cell A2 which is empty and the cell value changed to '0'.

I assume that row 3 has some values. Did you array enter it, commit with Ctrl-Shift-Enter?

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

[Bob Phillips]

No, row 2, row 3 and row 4 are completely empty.....

If row 3 is empty, the last column is 0, so it is correct

When you say array enter i'm not totally sure what you mean.

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.

I need this to be an automatic process with no manual intervention.

By virtue of being a worksheet formula, every time something in the target range changes, the formula will update.

[johnske]

Hi crmpicco,

If you want a VBA solution, try this

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

HTH,
John

[Steiner]

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 ?

[crmpicco]

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

that seems to be the same as:

ActiveSheet.UsedRange.Rows.Count

[johnske]

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.

Option Explicit

Sub Last_Find()
Dim Last As Long
Last = Cells.Find("*", SearchOrder:=xlByColumns, _
LookIn:=xlValues, SearchDirection:=xlPrevious).Column
MsgBox Last
End Sub

HTH,
John

[crmpicco]

that works great - thanks again.

[johnske]

Not a prob., glad to be able to help