Row count for table after filtering

[remeng]

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:

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:

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

 

[mintjulep]

Why not just use countif()?

 

[combo]

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

 

[remeng]

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?

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

 

[combo]

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

combo

 

[remeng]

Yes it does Combo

 

[Andrzejek]

A Star for Combo...?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[remeng]

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.

 

[remeng]

I found something that seems to be working.

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