过滤数据的行数 [英] Row count on the Filtered data

问题描述

我正在使用下面的代码来获取 VBA 中过滤数据行的计数，但是在获取计数时，它给出了运行时错误显示:

I'm using the below code to get the count of the filtered data rows in VBA, but while getting the count, it's giving the run time error showing:

需要对象".

可以请一些人告诉我需要哪些更改吗?

Could some please let me know what change(s) is needed?

Set rnData = .UsedRange

With rnData
    .AutoFilter Field:=327, Criteria1:=Mid(provarr(q), 1, 2)
    .Select
    .AutoFilter Field:=328, Criteria1:=Mid(provarr(q), 3, 7)
    .Select
    .AutoFilter Field:=330, Criteria1:=Mid(provarr(q), 10, 2)
    .Select
    .AutoFilter Field:=331, Criteria1:=Mid(provarr(q), 12, 2)
    .Select

     Rowz = .AutoFilter.Range.SpecialCells(xlCellTypeVisible).Rows.count

     ....
End With

推荐答案

如果您尝试像这样计算已经自动过滤的范围内的行数:

If you try to count the number of rows in the already autofiltered range like this:

Rowz = rnData.SpecialCells(xlCellTypeVisible).Rows.Count

它只会计算自动过滤范围的第一个连续可见区域中的行数.例如.如果自动筛选范围是第 1 行到第 10 行，并且筛选了第 3、5、6、7 和 9 行，则有四行可见(第 2、4、8 和 10 行)，但它会返回 2，因为第一个连续的可见行范围是第 1 行(标题行)和第 2 行.

It will only count the number of rows in the first contiguous visible area of the autofiltered range. E.g. if the autofilter range is rows 1 through 10 and rows 3, 5, 6, 7, and 9 are filtered, four rows are visible (rows 2, 4, 8, and 10), but it would return 2 because the first contiguous visible range is rows 1 (the header row) and 2.

更准确的替代方法是(假设 ws 包含带有过滤数据的工作表):

A more accurate alternative is this (assuming that ws contains the worksheet with the filtered data):

Rowz = ws.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1

我们必须减去 1 才能删除标题行.我们需要将标题行包含在我们的计数范围内，因为如果没有找到单元格，SpecialCells 会抛出错误，这是我们想要避免的.

We have to subtract 1 to remove the header row. We need to include the header row in our counted range because SpecialCells will throw an error if no cells are found, which we want to avoid.

与 Rows 属性不同，即使范围有多个区域，Cells 属性也会为您提供准确的计数.所以我们只取自动过滤范围的第一列并计算可见单元格的数量.

The Cells property will give you an accurate count even if the Range has multiple Areas, unlike the Rows property. So we just take the first column of the autofilter range and count the number of visible cells.

这篇关于过滤数据的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！