Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Row count for table after filtering

Status
Not open for further replies.
remeng

remeng

Technical User
Jul 27, 2006
518
US
Hello,

I have run into an issue with counting the number of visible rows in a table after it has been filtered. I can get the total row count but not the visible rows only.

I've tried several methods as well, but everything that I've research uses a column range like "A2:A" which isn't helpful in this cause since the name of the table's column is dynamic.


Worksheet Nam variable example: part_path_worksheet_name
Table Name variable example: part_path_number_table
Column Name: "Part Path"
Total row count unfiltered without column header: 510
Expected row count after without column header filter: 40

I do not use the total column row count - 1 method since the .ListsObjects method works for the normal column count.

Filter Code:

Code: 
ThisWorkbook.Sheets(part_path_worksheet_name).ListObjects(part_path_number_table).Range.AutoFilter Field:=rules_finish_length_min_index, _
        Criteria1:=rules_finish_length_min_value

one of the row count codes I've tried:

Code: 
rule_finish_length_min_result_count = ThisWorkbook.Sheets(part_path_worksheet_name).ListObjects(part_path_number_table).Range("part_path_number_table[Part Path]").SpecialCells(xlCellTypeVisible).Rows.Count

I figure that it is a simple solution that I am overlook. My Google Foo has been a little off recently.

Thanks for the help,

Mike
 
Sort by date Sort by votes
There is no dedicated property of range or list object that returns number of hidden/visible records. A slight simplification: replace Range("part_path_number_table[Part Path]") by DataBodyRange:
... [tt].ListObjects(part_path_number_table).DataBodyRange.SpecialCells(xlCellTypeVisible).Rows.Count[/tt]

combo
 
Upvote 0 Downvote
Hi Combo,

Thanks for always being there to help out.

I might have entered this incorrectly since the return value was 3 instead of 40. Could you check this and let me know if I made an error?

Code: 
rule_finish_length_min_result_count = ThisWorkbook.Sheets(part_path_worksheet_name).ListObjects(part_path_number_table).DataBodyRange.SpecialCells(xlCellTypeVisible).Rows.Count



hi mintjulep,

The table has a dynamic name captured in other sections of the code. There are several table names that could be returned. countif() as a application formula isn't as flexible for this particular instance.



Mike
 
Upvote 0 Downvote
[tt]ThisWorkbook.Sheets(part_path_worksheet_name).ListObjects(part_path_number_table).DataBodyRange.Rows.count[/tt]
returns 510, as expected?

combo
 
Upvote 0 Downvote
A Star for Combo...?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Upvote 0 Downvote
Hi Andy,

The filtered results isn't working correctly. When Combo's code is run, the total column count is the result instead of the filtered result.

Unfortunately it is still showing the incorrect result.

The correct result should have resulted in 40 out of 510.
 
Upvote 0 Downvote
I found something that seems to be working.

Code: 
rule_finish_length_min_result_count = ThisWorkbook.Sheets(part_path_worksheet_name).ListObjects(part_path_number_table).DataBodyRange.Columns(part_path_index).SpecialCells(xlVisible).Cells.Count

xlVisible seems to be the key difference as well as calling out a specific column (part_path_index).

Even if the column does not have a value, but the row still exists, the row will still be counted.

Thank you everyone!

Link to article with the steps I followed: Link
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Question
Delete dynamic table rows without removing password protection
Replies
7
Views
232
remeng
remeng
MattGreer
  • Locked
  • Question
Delete a Table Row in a Filtered Table 2
Replies
11
Views
384
SkipVought
S
remeng
  • Solved
Combine two working macros into one for MS Word 1
Replies
7
Views
425
strongm
strongm
remeng
  • Locked
  • Question
Excel VBA to Pass Var between file macros after var changes
Replies
13
Views
276
mikrom
mikrom
dgillz
  • Locked
  • Question
Back coloring an entire row if a duplicate is found in a dynamic range 1
Replies
18
Views
316
SkipVought
S

Part and Inventory Search

Sponsor

Back
Top