Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
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. –  Breakthrough Jul 30 '11 at 15:11

4 Answers 4

up vote 4 down vote accepted

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
share|improve this answer
    
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. –  music2myear Aug 15 '11 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...). –  Breakthrough Aug 15 '11 at 16:53
    
Ok. I've got it. Thank you for this thorough description of the problem and solution. –  music2myear Aug 15 '11 at 18:53

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
share|improve this answer
1  
Yeap... it seems that the topic says one thing, the function names another... and the code itself yet another thing. –  Tiago Cardoso Jul 30 '11 at 14:19
    
Thanks, that made me laugh. I think that comment could apply to a lot of questions. –  PerryJ Jul 30 '11 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. –  music2myear Aug 15 '11 at 17:26

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

share|improve this answer
    
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". –  Breakthrough Jul 30 '11 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. –  Tiago Cardoso Jul 30 '11 at 16:46
1  
sorry, didn't see that - good call. Gotta love people who use horribly ambiguous names in their code. –  Breakthrough Jul 30 '11 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). –  Tiago Cardoso Jul 30 '11 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. –  music2myear Aug 15 '11 at 17:28

Try:

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

Rows is a property.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.