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
Determining The Number Of Visible Rows In A Table
-
- 2StarLounger
- Posts: 168
- Joined: 03 Oct 2015, 00:30
-
- Administrator
- Posts: 78728
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Determining The Number Of Visible Rows In A Table
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])")
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 153
- Joined: 11 Jun 2012, 20:37
Re: Determining The Number Of Visible Rows In A Table
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!
intersect(accounts.DataBodyRange.Columns(1),rngVisibleRows).cells.Count
but of course, column(1) of the listobject should not have been hidden!
-
- 2StarLounger
- Posts: 168
- Joined: 03 Oct 2015, 00:30
Re: Determining The Number Of Visible Rows In A Table
Hans and p45cal:
Thanks for your responses. Your solutions work!
Regards,
Rich Locus, Logicwurks.com
Thanks for your responses. Your solutions work!
Regards,
Rich Locus, Logicwurks.com