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