New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Last Used row in a Worksheet Column

Free Excel Macros

Last Used row in a Worksheet Column

Postby Sisyphus » Tue Jan 31, 2012 10:26 pm

This code will return the last used row in column C of the ActiveSheet:-
Code: Select all
Cells(Rows.Count, "C").End(xlUp).Row

The following code will return the number of the row in the longest column in the ActiveSheet.
Code: Select all
Cells.Find("*", Cells(1), xlFormulas, xlWhole, xlByRows, xlPrevious).Row

Neither of these functions is capable of returning zero when there is no last used row to report. The first will return a wrong result, 1, which is the same as when one row was used, the second will throw an error. If the last row 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. I hardly ever write a project without it.
Code: Select all
Function LastRow(Optional Col As Variant, _
                 Optional Ws As Worksheet) As Long
    ' 0059 V 3.5   Revised Jan 05, 2014
   
    '   This function will return 0 if no cells have been used
    '   in the specified column.
    '   If Col isn't specified or specified as 0 or "" or "0"
    '   the last used row in any column will be returned.
    '   Therefore the next empty row is always LastRow + 1
    '   If Col doesn't specify a column available in Ws
    '   this function will return -1
   
    ' ==================================================
    '   Parameters:
    '       Col         = The column from which to return the last used row.
    '                     Accepts strings (incl. multiple characters) or numbers
    '       Ws          = Worksheet in which Col exists
    ' ==================================================
   
    Dim IsZero As Boolean
    Dim Rng As Range
    Dim R As Long
    Dim C As Long
   
    ' ==================================================
   
    If Ws Is Nothing Then Set Ws = ActiveSheet
    IsZero = IsMissing(Col)
    If Not IsZero Then
        C = Val(Col)
        If C = 0 Then IsZero = IsNumeric(Col) Or (Len(Col) = 0)
        If Not IsZero Then
            On Error Resume Next            ' if string is invalid (like "@1")
            C = Range(Col & 1).Column
            If C < 1 Or C > Ws.Columns.Count Then
                ' Columns.Count is 256 prior to Excel 2007
               LastRow = -1
                Exit Function
            End If
        End If
    End If
   
    On Error GoTo 0
    With Ws
        If IsZero Then
            Set Rng = .Cells.Find("*", .Cells(1), xlFormulas, _
                                  xlWhole, xlByRows, xlPrevious)
        Else
            Set Rng = .Cells(.Rows.Count, C).End(xlUp)
            ' return zero if no row was used in column C
            If Rng.Row = 1 And Len(Rng.Formula) = 0 Then Set Rng = Nothing
        End If
        If Not Rng Is Nothing Then
            With Rng
                ' include all rows of a merged range
                R = .Row + .MergeArea.Rows.Count - 1
            End With
        End If
    End With
    LastRow = R
End Function

Just paste the function into your project and use it as you might call a variable. For example:
Code: Select all
    For MyRow = 1 to LastRow
        ' do your stuff
    Next MyRow
Called, as above, without any parameters, the last row of the longest column in the ActiveSheet will be returned. LastRow("AA") or LastRow(27) will return the last used row from column AA in the ActiveSheet, and LastRow("ABC", Sheets("Sheet1")) will return the last used row from column number 731 in Sheet1. The next row to use is always LastRow + 1 and the function will never return an error. Erroneous calls like LastRow("ZZZ") or LastRow("??") will return -1.

Updated by Sisyphus:-
January 5, 2014
Earlier versions defaulted to column A if no column was specified.
After version 3.4 sported some inaccuracies the code in the
above version 3.5 was almost completely re-designed.


LastRow has a sister function LastColumn which you can find at this link,
Code: Select all
http://www.excelkey.com/forum/viewtopic.php?f=5&t=410&p=1623#p1623
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 30 guests