Why your method doesn't work: there are two reasons here. The first, when you put Rows.Count
, there is no reference for Rows
- it is a property of a Range
. To fix it, you just have to reference the same range as you already are (just append it before you call Rows.Count
like this:
Function FindLastDataLine(strColName As String) As Long
FindLastDataLine = Range(strColName).Offset(Range(strColName).Rows.Count - 1, 0).End(xlUp).Row
End Function
The second reason is that you are using Offset
. Offset
literally shifts a Range
by how much you tell it. You don't want to shift the whole range of cells, but find the last cell in the range. You can do this pretty simply by changing Offset
to Cells
, removing the initial Range()
call (since we're going to choose one cell), and changing 0
to the column you want. However, because you pass the column as "A:A"
, that's not possible, so you would have to replace it with Range(strColName).Column
as follows:
Function FindLastDataLine(strColName As String) As Long
FindLastDataLine = Cells(Range(strColName).Rows.Count, Range(strColName).Column).End(xlUp).Row
End Function
A better solution: the following solution will work on all recent versions of MS Office (2003, 2007, and 2010), and will handle errors. You call it by passing either the column letter, or column number:
Function GetLastDataRow(col As Variant) As Long
GetLastDataRow = -1
If (IsNumeric(col) And col >= 1) Or Len(col) <= 2 Then
On Error Resume Next
GetLastDataRow = _
Cells(Cells(1, col).EntireColumn.Rows.Count, col).End(xlUp).Row
On Error GoTo 0
End If
End Function
The following illustrates how you would call this function, and some example outputs. Let's assume that the whole sheet is clear, except for some random data entered in cells B1
to B8
and B10
(B9
is left blank). Note that you don't enter the column as a range, but rather the column letter or the column number (invalid values return -1):
GetLastDataRow(1) = 1 GetLastDataRow("A") = 1
GetLastDataRow(2) = 10 GetLastDataRow("B") = 10
GetLastDataRow("AX") = 1 GetLastDataRow("A:X") = -1
GetLastDataRow("Oops...") = -1 GetLastDataRow(200) = 1
As a technical note, if the Cells
method fails, it is assumed that the input was invalid, so the function returns -1. I urge you to use this practice (return invalid values if the input was invalid) in your function, it will greatly help you to avoid errors in the future.
How this works, it finds the last possible row in any particular column (depends on your version of MS Office), and then uses the End
method to find the last cell in that column with data.
Here is an alternative version which will return 0 if all the cells in that column are blank:
Function GetLastDataRow(col As Variant) As Long
GetLastDataRow = -1
If (IsNumeric(col) And col >= 1) Or Len(col) <= 2 Then
On Error Resume Next
If IsEmpty(Cells(Cells(1, col).EntireColumn.Rows.Count, col).End(xlUp).Value) Then
GetLastDataRow = 0
Else
GetLastDataRow = _
Cells(Cells(1, col).EntireColumn.Rows.Count, col).End(xlUp).Row
End If
On Error GoTo 0
End If
End Function
Example output:
GetLastDataRow(1) = 0 GetLastDataRow("A") = 0
GetLastDataRow(2) = 10 GetLastDataRow("B") = 10
GetLastDataRow("AX") = 0 GetLastDataRow("A:X") = -1
GetLastDataRow("Oops...") = -1 GetLastDataRow(200) = 0
Option Explicit
at the top of your code, and explicitly declare variables. I cannot state how vitally important that practice is as a programmer.