PDA

View Full Version : [SOLVED] Find the last used column



crmpicco
05-18-2005, 02:50 AM
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
05-18-2005, 02:54 AM
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
05-18-2005, 02:56 AM
sorry, how do you mean a worksheet function. i kind of need to this automatically for each sheet. any ideas?

Bob Phillips
05-18-2005, 02:57 AM
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
05-18-2005, 03:03 AM
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
05-18-2005, 03:24 AM
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
05-18-2005, 03:36 AM
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
05-18-2005, 03:47 AM
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
05-18-2005, 04:33 AM
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
05-18-2005, 07:27 AM
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
05-20-2005, 02:00 AM
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
05-20-2005, 02:59 AM
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
05-20-2005, 03:59 AM
that works great - thanks again.

johnske
05-20-2005, 05:06 AM
Not a prob., glad to be able to help :beerchug: