Determining The Number Of Visible Rows In A Table

[richlocus]

Hello!
Let's say I have a table with 20 rows, each representing a sales rep.
Suppose I filter it so that I only report on 6 of the sales reps.

The code below shows how I can loop through the table and only see the visible (selected) sales reps.

I have not found a way to know the count of visible rows selected BEFORE I actually loop through them. After I apply a filter, is there a VBA command to say that only 6 rows are available before I start looping through them? I get around this by keeping a count of each row that I loop through as shown in the code below. Once I know the count, then I proceed with the program logic.

Thanks in advance!
Rich Locus

Here's the code:

Sub SelectVisibleAccountObjects()
' **********************************************************************
' Save The Selected Visible Accounts To The Accounts Table
' Allow A Maximum of 6 Accounts
' **********************************************************************
Dim strAccount(24) As String
Dim VisibleAccounts
Dim i As Long
Dim Accounts As ListObject, rngVisibleRows As Range, rw As Range

Set Accounts = Worksheets("Accounts").ListObjects("RichTable")
' *****************************************************************************
' Ignore Errors In Case No Accounts Are Selected To Prevent System Cancellation
' *****************************************************************************
On Error Resume Next

' *****************************************************************************
' Set The Range For Visible Rows
' *****************************************************************************
Set rngVisibleRows = Accounts.DataBodyRange.SpecialCells(xlCellTypeVisible)

' ************************************************************************************************************
' Turn Error Flagging Back On
' ************************************************************************************************************
On Error GoTo 0

' ************************************************************************************************************
' The Following Is Not Normally Possible Since Excel Requires At Least One Row Selected
' In A Table (Added For Historical Purposes)
' ************************************************************************************************************
If rngVisibleRows Is Nothing Then
MsgBox "No Accounts Selected - Job Cancelled"
Exit Sub
End If

' **************************************************************************************************************
' Loop Through Each Visible Object (Without knowing how many rows there are in advance)
' **************************************************************************************************************
For Each rw In rngVisibleRows.Rows
Debug.Print rw.Cells(1).Value
Next rw

End Sub

[HansV]

Let's say your table has a column ID that is guaranteed to be filled. You can then use

Code: Select all

    Dim NumRows As Long
    NumRows = Evaluate("SUBTOTAL(103,RichTable[ID])")

[p45cal]

Yes, rngVisibleRows.rows.count only gives the number of rows in the first area, however rngVisibleRows.cells.count gives a count of all cells in the visible area, so if you only look at one column you can do something like:
intersect(accounts.DataBodyRange.Columns(1),rngVisibleRows).cells.Count
but of course, column(1) of the listobject should not have been hidden!

[richlocus]

Hans and p45cal:
Thanks for your responses. Your solutions work!
Regards,
Rich Locus, Logicwurks.com