- Code: Select all
Cells(2, Columns.Count).End(xlToLeft).Column
The following code is supposed to return the number of the last column in the longest row in the ActiveSheet.
- Code: Select all
Cells.Find("*", Cells(1), xlFormulas, xlWhole, xlByColumns, xlPrevious).Column
Actually, my tests have shown this function to be unreliable. I have better results with UsedRange in place of Cells. However, neither of these functions is capable of returning zero when there is no last used column to report, in a blank row. The first will return a wrong result, 1, which is the same as when one column was used, the second will throw an error. If the last column is in a merged area both functions fail completely. They simply ignore whatever rows might be merged with the nominal last used.
The following function has none of these problems. It is easier to use, has a simpler syntax (even when accessing other sheets) and combines the virtues of both the above into one.
- Code: Select all
Function LastColumn(Optional R As Long, _
Optional Ws As Worksheet) As Long
' 0056 V 2.0 Created 05 Jan, 2014
' This function will return 0 if no cells have been used
' in the specified row.
' If R isn't specified or 0 the last used column in any row
' of the specified sheet will be returned.
' Therefore the next empty column is always LastColumn + 1
' If R doesn't specify a row available in Ws this function will return -1
' If the an Excel version prior to 2007 was used to create the
' spreadsheet Rows.Count may be 65536 even if viewed with a
' later Excel version.
' ==================================================
' Parameters:
' R = The row from which to return the last used column
' Ws = Worksheet object in which R exists
' ==================================================
Dim Rng As Range
Dim C As Long
' ==================================================
If Ws Is Nothing Then Set Ws = ActiveSheet
If R > Ws.Rows.Count Then
C = -1
Else
With Ws
If R Then
Set Rng = .Cells(R, .Columns.Count).End(xlToLeft)
' return zero if no column was used in row R
If Rng.Column = 1 And Len(Rng.Formula) = 0 Then Set Rng = Nothing
Else
With .UsedRange
Set Rng = .Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByColumns, xlPrevious)
End With
End If
If Not Rng Is Nothing Then
With Rng
' include all columns, if Rng is merged
C = .Column + .MergeArea.Columns.Count - 1
End With
End If
End With
End If
LastColumn = C
End Function
Just paste the function into your project and use it as you might call a variable. For example:
- Code: Select all
For MyColumn = 1 to LastLastColumn
' do your stuff
Next MyColumn
Updated by Sisyphus:-
January 5, 2014
Not being as popular as the sister function LastRow this function
has long been neglected. With the above version it is brought up to
the same standard. Its predesessor defaulted to row 1 when no R-
parameter was supplied.
You can find the sister function LastRow at this link,
- Code: Select all
http://www.excelkey.com/forum/viewtopic.php?f=5&t=393&p=1569#p1569