CODEWITHSUNDEEP
vbaexcelexcel-2010
Kai
Kai

Reputation: 2168

ListObject.DataBodyRange.SpecialCells(xlCellTypeVisible).Rows.count returns wrong value

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.

Upvotes: 3

Views: 18837

Answers (1)

Rory
Rory

Reputation: 34055

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

Upvotes: 9

Related Questions