4

I'm trying to learn functions for the purpose of simplifying and reusing code whenever necessary.

I began by turning something I use pretty often into a function: Returning the integer value of the last non-blank column in a spreadsheet.

Function FindLastDataLine(strColName As String) As Long
    FindLastDataLine = Range(strColName).Offset(Rows.Count - 1, 0).End(xlUp).Row
End Function

Sub PracticeMacro()
    intItemCount = FindLastDataLine("A:A")
    MsgBox ("There are " & intItemCount & " rows of data in column A.")
End Sub

When I run this I receive the runtime error '1004' "Application-defined or object-defined error" which Help helpfully defines as "someone else's fault" to quote not quite verbatim.

Where might I be going wrong?

2
  • Always use Option Explicit at the top of your code, and explicitly declare variables. I cannot state how vitally important that practice is as a programmer. Jul 30, 2011 at 15:11
  • Thank you @Breakthrough. In the years since this comment I have made it a habit to do so. Dec 21, 2016 at 17:22

4 Answers 4

4

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
3
  • Ok, I'm sorry it's taken so freakishly long to get back on this, but here goes. A question: Refering to the first part of your answer, if I passed FindLastDataLine("A:1") the current system would work because it's specifying cell one which Row can then work rather than a range? This is focusing just on the first part in order that I may better understand your suggestion. Aug 15, 2011 at 16:47
  • I'm sorry, I don't quite understand your question... "A:1" by itself is not a valid range. Before the custom function I wrote, you need to pass a valid range as a string (i.e. A:A, A2:A15, etc...). Aug 15, 2011 at 16:53
  • Ok. I've got it. Thank you for this thorough description of the problem and solution. Aug 15, 2011 at 18:53
1

You wrote "Returning the integer value of the last non-blank row in a spreadsheet." But it looks like you are trying to get the last non-blank row in a particular column of a spreadsheet.

If you change your function to this, I think it will work:

Function FindLastDataLine(strColName As String) As Long
    FindLastDataLine = ActiveSheet.Cells(ActiveSheet.Rows.Count, strColName).End(xlUp).Row
End Function
3
  • 1
    Yeap... it seems that the topic says one thing, the function names another... and the code itself yet another thing. Jul 30, 2011 at 14:19
  • Thanks, that made me laugh. I think that comment could apply to a lot of questions.
    – PerryJ
    Jul 30, 2011 at 15:31
  • I'm sorry, I was indeed looking for the integer value of the last non-black cell in a column, not a row. My bad. Aug 15, 2011 at 17:26
1

It may not fix your error, but would return the ItemCount within the range...

Function FindLastDataLine(strColName As String) As Long
    FindLastDataLine = Evaluate("COUNTA(" & strColName & ")")
End Function

Sub PracticeMacro()
    intItemCount = FindLastDataLine("A:A")
    MsgBox ("There are " & intItemCount & " rows of data in column A.")
End Sub

Notice there are some other ways to call this COUNTA Excel function...

5
  • This doesn't take into account blank columns, so it doesn't even come close to the original poster's "returning the integer value of the last non-blank row in a spreadsheet". Jul 30, 2011 at 15:49
  • Take a look at what the message box says... MsgBox ("There are " & intItemCount & " rows of data in column A."). AFAIK, COUNTA does exactly this. Take a look @ my comment to @PerryJ post. Jul 30, 2011 at 16:46
  • 1
    sorry, didn't see that - good call. Gotta love people who use horribly ambiguous names in their code. Jul 30, 2011 at 16:49
  • NW. I believe that @music2myear goal was to test his skills rather than do a specific development... so I'd guess he has a 'default' code his tweaking over an over again (but without updating some pieces that could help us out, like the message box). Jul 30, 2011 at 16:54
  • I'm working on specific tasks. There is an existing macro that performs the task correctly. However, it uses several extremely inefficient methods. My goal is to go through and rebuild the macro to perform the task much more efficiently as well as to roll several pre- and post-macro processes into the macro itself. The process this applies to should go from a 5 minute involved task to a 30 second hands-off task. Aug 15, 2011 at 17:28
0

Try:

FindLastDataLine = _
     Range(strColName).Offset(ActiveSheet.Rows.Count - 1, 0).End(xlUp).Row

Rows is a property.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .