代码之家 › 专栏 › 技术社区 › Nimy Alex

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

excel vba

1

Nimy Alex · 技术社区 · 8 年前

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

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

但在调试时,我得到一个错误,即没有对象集。我的代码有错误吗?

4 回复 | 直到 8 年前

1

QHarr 8 年前

像这样

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

2

1

Ibo 8 年前

使用这些函数,您始终可以找到最后一行和最后一列,您可以输入您正在查找的最后一行或最后一列,更重要的是,您可以使用哪一列或哪一行作为您的标准。如果省略参数,它将使用活动工作表作为工作表,并使用第一列查找最后一行/单元格,第一行查找最后一列/单元格

Function LastRowInColumn(Optional sh As Worksheet, Optional colNumber As Long = 1) As Long
    'Finds the last row in a particular column which has a value in it
    If sh Is Nothing Then
        Set sh = ActiveSheet
    End If
    LastRowInColumn = sh.Cells(sh.Rows.Count, colNumber).End(xlUp).row
End Function

Function LastColumnInRow(Optional sh As Worksheet, Optional rowNumber As Long = 1) As Long
    'Finds the last column in a particular row which has a value in it
    If sh Is Nothing Then
            Set sh = ActiveSheet
        End If
    LastColumnInRow = sh.Cells(rowNumber, sh.Columns.Count).End(xlToLeft).Column
End Function

3

1

tigeravatar 8 年前

这是我使用的:

Public Function GetLastRow(ByVal arg_ws As Worksheet) As Long

    Dim rTest As Range

    Set rTest = arg_ws.Cells.Find("*", arg_ws.Range("A1"), xlFormulas, xlPart, , xlPrevious)
    If Not rTest Is Nothing Then GetLastRow = rTest.Row Else GetLastRow = 0

End Function

这样称呼它:

Sub tst()

    MsgBox GetLastRow(ActiveWorkbook.Sheets("Sheet1"))

End Sub

4

0

tigeravatar 8 年前

完全引用父工作表,并使用长而不是整数。结束(xlup)是多余的。

Private Function GetLastRow(sheetName As String) As Long
    Dim lastRow As long

    with ActiveWorkbook.workSheets(sheetName)
        lastRow = .Cells.Find(What:="*", _
                              After:=.Range("A1"), _
                              LookAt:=xlPart, _
                              LookIn:=xlFormulas, _
                              SearchOrder:=xlByRows, _
                              SearchDirection:=xlPrevious, _
                              MatchCase:=False).Row
    end with
    GetLastRow = lastRow
End Function