New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Last Used Column

Free Excel Macros

Last Used Column

Postby Sisyphus » Wed Feb 01, 2012 7:25 pm

This code will return the last used column in row 2 of the ActiveSheet:-
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
Called, as above, without any parameters, the last column of the longest row in the ActiveSheet will be returned. LastColumn(2) will return the last used column from row 2 in the ActiveSheet, and LastColumn(10, Sheets("Sheet1")) will return the last used column from row 10 in Sheet1. The next column to use is always LastColumn + 1 and the function will never return an error unless a non-numeric value is fed to it as an argument. Erroneous calls like LastColumn(-1) or LastColumn(3000000) will return -1.

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
Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Return to Excel Macros

Who is online

Users browsing this forum: No registered users and 28 guests