vba - 如果工作表中存在数据，则返回最后一行

Question

我正在编写一个函数，它将检查已经存在的工作表是新的还是有数据的。如果它包含数据，那么它应该返回最后一行，否则它必须返回第一行。我正在使用以下代码:

Private Function GetLastRow(sheetName As String) As Integer
Dim lastRow As Integer
lastRow = CurrentWorkbook.Sheets(sheetName).Cells.Find(What:="*", _
                After:=Range("A1"), _
                LookAt:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).End(xlUp).Row

GetLastRow = lastRow
End Function

但是在调试时，我得到一个错误，说没有对象集。
我的代码有错误吗？

Answer 1

像这样

Option Explicit

Public Sub TEST()

Debug.Print GetLastRow(ActiveSheet.Name)

End Sub

Private Function GetLastRow(ByVal sheetName As String) As Long

    Dim lastRow As Long

    With ActiveWorkbook.Sheets(sheetName)

        On Error GoTo returnVal

        lastRow = .Cells.Find(What:="*", _
                              After:=.Range("A1"), _
                              LookAt:=xlPart, _
                              LookIn:=xlFormulas, _
                              SearchOrder:=xlByRows, _
                              SearchDirection:=xlPrevious, _
                              MatchCase:=False).Row
    End With

    GetLastRow = lastRow

    Exit Function

returnVal:

    GetLastRow = 1

End Function