vba 过滤数据的行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17285897/
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
Row count on the Filtered data
提问by user2300403
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:
我正在使用下面的代码来获取 VBA 中过滤数据行的计数，但是在获取计数时，它给出了运行时错误显示：
"Object required".
“所需对象”。
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
回答by Jon Crowell
If you try to count the number of rows in the already autofiltered range like this:
如果您尝试像这样计算已经自动过滤的范围中的行数：
Rowz = rnData.SpecialCells(xlCellTypeVisible).Rows.Count
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.
它只会计算自动过滤范围的第一个连续可见区域中的行数。例如，如果自动筛选范围是第 1 行到第 10 行，并且筛选了第 3、5、6、7 和 9 行，则有四行是可见的（第 2、4、8 和 10 行），但它会返回 2，因为第一个连续的可见范围是第 1 行（标题行）和第 2 行。
A more accurate alternative is this (assuming that wscontains the worksheet with the filtered data):
一个更准确的替代方法是（假设ws包含带有过滤数据的工作表）：
Rowz = ws.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1
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.
我们必须减去 1 才能删除标题行。我们需要在我们的计数范围中包含标题行，因为如果没有找到单元格，SpecialCells 会抛出错误，这是我们想要避免的。
The Cellsproperty will give you an accurate count even if the Range has multiple Areas, unlike the Rowsproperty. So we just take the first column of the autofilter range and count the number of visible cells.
与Cells属性不同，即使范围有多个区域，该属性也会为您提供准确的计数Rows。所以我们只取自动过滤范围的第一列并计算可见单元格的数量。
回答by Frank
Simply put this in your code:
只需将其放入您的代码中：
Application.WorksheetFunction.Subtotal(3, Range("A2:A500000"))
Make sure you apply the correct range, but just keep it to ONE column
确保您应用了正确的范围，但只需将其保留在一列
回答by Joe Stellato
While I agree with the results given, they didn't work for me. If your Table has a name this will work:
虽然我同意给出的结果，但它们对我不起作用。如果您的表有一个名称，这将起作用：
Public Sub GetCountOfResults(WorkSheetName As String, TableName As String)
Dim rnData As Range
Dim rngArea As Range
Dim lCount As Long
Set rnData = ThisWorkbook.Worksheets(WorkSheetName).ListObjects(TableName).Range
With rnData
For Each rngArea In .SpecialCells(xlCellTypeVisible).Areas
lCount = lCount + rngArea.Rows.Count
Next
MsgBox "Autofilter " & lCount - 1 & " records"
End With
Set rnData = Nothing
lCount = Empty
End Sub
This is modified to work with ListObjects from an original version I found here:
这被修改为与我在这里找到的原始版本的 ListObjects 一起使用：
回答by Josh
I know this an old thread, but I found out using the Subtotal method in VBA also accurately renders a count of the rows. The formula I found is in this article, and looks like this:
我知道这是一个旧线程，但我发现在 VBA 中使用 Subtotal 方法也可以准确地呈现行数。我找到的公式在这篇文章中，看起来像这样：
Application.WorksheetFunction.Subtotal(2, .Range("A2:A" & .Rows(.Rows.Count).End(xlUp).Row))
I tested it and it came out accurately every time, rendering the correct number of visible rows in column A.
我对其进行了测试，每次都准确地显示出来，在 A 列中呈现正确数量的可见行。
Hopefully this will help some other wayfarer of the 'Net like me.
希望这会帮助像我这样的“网络”的其他旅行者。
回答by warsong
I have found a way to do this that it requires 2 steps, but it works
我找到了一种方法来做到这一点，它需要 2 个步骤，但它有效
' to copy out a filtered selection into a different sheet
number_of_dinosaurs = WorksheetFunction.Count(Worksheets("Dinosaurs").Range("A2", "A3000"))
With Worksheets("Dinosaurs")
.AutoFilterMode = False
With .Range("$A:$E$" & number_of_dinosaurs)
.AutoFilter Field:=2, Criteria1:="*teeth*" ' change your criteria to whatever you like
.SpecialCells(xlCellTypeVisible).Copy Destination:=Worksheets("Bad_Dinosaurs").Range("A1")
End With
End With
' then do a normal count on the secondary sheet
number_of_dinosaurs_that_eat_humans = WorksheetFunction.Count(Worksheets("Bad_Dinosaurs").Range("A2", "A30000"))
回答by Sudhanshu Soni
Rowz = Application.WorksheetFunction.Subtotal(2, Range("A2:A" & Rows(Rows.Count).End(xlUp).Row))
This worked for me quite well
这对我很有效
回答by Marc Quattrini
I would think that now you have the range for each of the row, you can easily manipulate that range with the offset(row, column) action? What is the point of counting the records filtered (unless you need that count in a variable)? So instead of (or as well as in the same block) write your code action to move each row to an empty hidden sheet and once all done, you can do any work you like from the transferred range data?
我认为现在您拥有每一行的范围，您可以使用 offset(row, column) 操作轻松操纵该范围？对过滤的记录进行计数有什么意义（除非您需要在变量中进行计数）？因此，而不是（或在同一块中）编写代码操作以将每一行移动到一个空的隐藏工作表，一旦全部完成，您就可以从传输的范围数据中执行任何您喜欢的工作？
