I have a filtered List Object and need to get the number of rows currently visible. I use this statement to get the number of lines:
MySheet.ListObjects("MyListObject").DataBodyRange.SpecialCells(xlCellTypeVisible).Rows.count
Most of the time it works. But when the table only has one or two rows visible, it always returns 1, even though it should return 2 when there are two rows. Is this a known issue? If so, are there any workarounds?
I'd rather avoid doing a manual loop through every row in the table to count, as it can be very large and this would be excessively slow.
Further info: The list object has a Totals row enabled, and is filtered with the following code:
'Remove existing filter
MySheet.ListObjects("MyListObject").Range.AutoFilter
'Apply new filter
MySheet.ListObjects("MyListObject").Range.AutoFilter Field:=1, Criteria1:=key
Where Field 1 is a (non-unique) key, and key is a String retrieved from elsewhere. I can physically see that there are two visible rows in the table (not including the header or totals row), yet .Rows.Count consistently returns 1 when there are 2 rows.
That code is incorrect - it will return the number of rows in the first visible contiguous block of cells in the filtered table. You should count the number of visible cells in one column only:
MySheet.ListObjects("MyListObject").DataBodyRange.Columns(1).SpecialCells(xlCellTypeVisible).count