excel - 查找 ListObject 中的最后一个可见行

Question

我正在使用下面的代码来查找 ListObject 中最后一个可见行的 Sheet.Row。它似乎工作正常，但我想知道当 ListObject 非常大时是否有更快的方法来做到这一点。

干杯

Function GetLastDataRowNumber(aListObj As ListObject, Optional bVisibleOnly As Boolean = True) As Long
CheckArgNotNothing aListObj, "aListObj"

Dim k As Long: k = aListObj.ListRows.Count
Dim lstRow As ListRow: Set lstRow = aListObj.ListRows(k)

GetLastDataRowNumber = lstRow.range.Row

If bVisibleOnly Then
    Do Until (lstRow.range.EntireRow.Hidden = False)
        k = k - 1
        If k = 0 Then
            ' no visible rows at all
            GetLastDataRowNumber = 0
            Exit Function
        End If
        Set lstRow = aListObj.ListRows(k)
    Loop
    GetLastDataRowNumber = lstRow.range.Row
End If

结束功能

更新

我今天不得不重新审视这个令人遗憾的话题。我的原始代码有效，但并非总是有效，而且表格非常大。寻找 RowHeight=0 是从尝试获取 Range.EntireRow.Hidden 时罕见但偶尔失败的升级。下面是更好的工作代码:

'modified from https://www.mrexcel.com/forum/excel-questions/593611-find-last-row-filtered-data.html
Function LastFilteredRowFor(anLo As ListObject) As Long
On Error GoTo NoFilterOnSheet
    With anLo.AutoFilter.Range.Columns(1)
        Dim rngVisible As Range: Set rngVisible = .Resize(.Rows.Count - 1).Offset(1, 0).SpecialCells(xlCellTypeVisible)
        LastFilteredRowFor = GetLastRowNbrFromSpecialCells(rngVisible.Address, anLo.ShowTotals)
    End With
NoFilterOnSheet:
End Function

Function GetLastRowNbrFromSpecialCells(anAddr As String, bHasTotalRow As Boolean) As Long
    Dim v As Variant: v = Split(Replace(anAddr, ",", ":"), ":")
    Dim k As Long: k = UBound(v)
    Dim sLastWord As String: sLastWord = v(k)
    If k = 0 Or (Not bHasTotalRow) Then
        If bHasTotalRow Then
            GetLastRowNbrFromSpecialCells = 0 'the word *is* the TotalRow, not what we want
        Else
            GetLastRowNbrFromSpecialCells = Range(sLastWord).Row
        End If
    Else
        Dim sLastDelim As String: sLastDelim = Mid(Right$(anAddr, Len(sLastWord) + 1), 1, 1)
        Select Case sLastDelim
            Case ":"
                GetLastRowNbrFromSpecialCells = Range(sLastWord).Offset(-1).Row
            Case ","
                GetLastRowNbrFromSpecialCells = Range(v(k - 1)).Row
        End Select
    End If
End Function

Answer 1

如果隐藏行的范围是连续的并且位于表的末尾，则可以避免循环:

Public Function GetLastDataRowNumber(ByRef aListObj As ListObject) As Long

    GetLastDataRowNumber = aListObj.Range.SpecialCells(xlCellTypeVisible).Rows.Count

End Function

.

否则，尝试直接访问行高属性( lstRow.Range.Height <> 0 ):

Public Function GetLastDataRowNumber(ByRef aListObj As ListObject, _
                                     Optional ByVal bVisibleOnly As Boolean = True) As Long

    CheckArgNotNothing aListObj, "aListObj"

    Dim k As Long, lstRow As ListRow

    k = aListObj.ListRows.Count
    Set lstRow = aListObj.ListRows(k)
    GetLastDataRowNumber = lstRow.Range.Row
    If bVisibleOnly Then
        Do Until (lstRow.Range.Height <> 0)
            k = k - 1
            If k = 0 Then
                GetLastDataRowNumber = 0
                Exit Function
            End If
            Set lstRow = aListObj.ListRows(k)
        Loop
        GetLastDataRowNumber = lstRow.Range.Row
    End If 
End Function