主页 / coding / 问题 / 79157339
Accepted
Forward Ed
Asked: 2024-11-05 08:12:05 +0800 CST

如何获取表格行号而不是工作表行号

  • 772

我刚刚问了一个关于获取已知列名的列号的问题，答案是.index

我正在尝试返回整个命名表的最后使用的行。如果我使用基于工作表的 .end(xlup) 方法，它将始终停在表的边缘，无论表的底部单元格是否为空。相反，我将其更改为以下内容：

我的代码：

Function TableLastUsedRow(TheTable As ListObject) As Long

Dim RowCount As Long, ColCount As Long, TableLastRow As Long

    With TheTable
        TableLastRow = .ListRows.count
    
        For ColCount = 1 To .ListColumns.count
            If IsEmpty(.DataBodyRange.Cells(TableLastRow, ColCount)) Then
                RowCount = .DataBodyRange.Cells(TableLastRow, ColCount).End(xlUp).Row
                If TableLastUsedRow < RowCount Then TableLastUsedRow = RowCount
            Else
                TableLastUsedRow = TableLastRow
                Exit Function
            End If
        Next ColCount
    End With

End Function

这对于返回工作表行引用非常有用。 如何获取表格的行号？

更新的代码行

RowCount = .DataBodyRange.Cells(TableLastRow, ColCount).End(xlUp).ListRow.Index

这将产生以下错误：

在此处输入图片描述

excel

2 个回答

  1. Best Answer

    Excel 表格中最后一个非空白（数据）行的索引

    Function TableLastUsedRow(ByVal TheTable As ListObject) As Long
    With TheTable
        Dim RowsCount As Long: RowsCount = .ListRows.Count
        ' The following line will exit the function and return 0.
        ' It might be better to return -1. You know how.
        If RowsCount = 0 Then Exit Function ' empty table
        With .DataBodyRange
            Dim lcell As Range:
            Set lcell = .Find("*", , xlValues, , xlByRows, xlPrevious)
            If lcell Is Nothing Then Exit Function ' all cells are blank
            ' Number of Populated (Data) Rows =
            '     Last Worksheet Row - First Worksheet Row + 1
            TableLastUsedRow = lcell.Row - .Row + 1
        End With
    End With
End Function
    • 2

  2. 不同的方法 - 只需在行上向后循环

    Sub Tester()
    Debug.Print LastUsedRowIndex(ActiveSheet.ListObjects(1))
End Sub


'Return the index of the last row in the table `TheTable`
' return zero if table is empty
Function LastUsedRowIndex(TheTable As ListObject) As Long
    Dim i As Long
    For i = TheTable.ListRows.Count To 1 Step -1
        If Application.CountA(TheTable.ListRows(i).Range) > 0 Then Exit For
    Next i
    LastUsedRowIndex = i
End Function
    • 2

相关问题