title: finding the last visible row in a ListObject
body: <p>I am using the code below to find the Sheet.Row of the last visible row in a ListObject. It seems to work fine but am wondering if there might be a quicker way to do this when the ListObject is very large.</p>
  
  <p>Cheers</p>
  
  <pre><code>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
  </code></pre>
  
  <p>End Function</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>I had to revisit this sorry subject today. My original code worked, but not always and not efficiently with very big tables. Looking for RowHeight=0 is an upgrade from rare but occasional failures when trying to get Range.EntireRow.Hidden. Below is better working code:</p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-html lang-html prettyprint-override"><code>'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</code></pre>
  </div>
  </div>
  </p>