vba ListObject.DataBodyRange.SpecialCells(xlCellTypeVisible).Rows.count 返回错误值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24137488/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ListObject.DataBodyRange.SpecialCells(xlCellTypeVisible).Rows.count returns wrong value
提问by Kai
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 timeit 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?
大多数时候它都有效。但是当表只有一两行可见时，它总是返回 1，即使当有两行时它应该返回 2 。这是一个已知的问题？如果是这样，是否有任何解决方法？
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 keyis a String retrieved from elsewhere. I can physically seethat there are two visible rows in the table (not including the header or totals row), yet .Rows.Countconsistently returns 1 when there are 2 rows.
其中字段 1 是（非唯一）键，key是从其他地方检索的字符串。我可以实际看到表中有两个可见行（不包括标题或总计行），但.Rows.Count在有 2 行时始终返回 1。
回答by Rory
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
