FIXED: Reference an Excel Sheet Name with a variable

Question

==============FIXED==========

I have a function:

Public Function getLastRow(sheetName As String) As Integer
    getLastRow = Worksheets(sheetName).Cells(Rows.count, 1).End(xlUp).row
End Function

When I try to use that function . . .

Sub test()
MsgBox getLastRow("Data")
End Sub

. . . I get a subscript out of Range (run-time error '9') error. How can I make the error go away?

running getLastRow = Worksheets("Data").Cells(Rows.count, 1).End(xlUp).row works fine. But I want to be able to dynamically choose the sheet name in my function.

Works fine for me too. Can you provide more info about your Excel version? Additionally you can try to split the expression into several parts to see where the problem is. — EFrank
Yeah, strange. I just copied the two functions to a blank workbook, and it worked, but is not working on my other workbook. — johnsimer
Are you sure the worksheet is called "Data"? For example, there's no whitespace before or after it? That could be the reason the "subscript out of range" occurs. — djikay
It now works on my other workbook! I'm not crazy, I swear. . . — johnsimer

Karol Marian Słuszniak Karol Marian Słuszniak · Accepted Answer · 2014-07-15T14:45:50

Problem is because incorrect workbook is activated.

try this:

Public Function getLastRow(sheetName As String) As Integer
    getLastRow = ThisWorkbook.Worksheets(sheetName).Cells(Rows.Count, 1).End(xlUp).Row
End Function

or

Public Function getLastRow(workbookName As String, sheetName As String) As Integer
    getLastRow = Workbooks(workbookName).Worksheets(sheetName).Cells(Rows.Count, 1).End(xlUp).Row
End Function

FIXED: Reference an Excel Sheet Name with a variable

1 Answers