vba 计算自动过滤后的可见行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23581946/
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
Counting the number of visible rows after autofilter
提问by theshizy
How do you count the number of visible/not null rows (from row 3 onwards, checking if column A is empty) after an autofilter? Right now I am only getting 26...
在自动过滤器之后，您如何计算可见/非空行的数量（从第 3 行开始，检查 A 列是否为空）？现在我只有26...
Full code:
完整代码：
Sub GetPrimaryContacts()
Dim Col As New Collection
Dim itm
Dim i As Long
Dim CellVell As Variant
'Get last row value
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
'Loop between all rows to get unique values
For i = 3 To LastRow
CellVal = Sheets("Master").Range("F" & i).Value
On Error Resume Next
Col.Add CellVal, Chr(34) & CellVal & Chr(34)
On Error GoTo 0
Next i
' Create workbooks - Token Not activated
Call TokenNotActivated
For Each itm In Col
ActiveSheet.Range("A2:Z2").Select
Selection.AutoFilter Field:=6, Criteria1:=itm
Call CountFilterAreaRows
Next
End Sub
回答by Doug Glancy
Here's a function that will count the visible rows in an autofiltered range, even if there are none:
这是一个函数，它会计算自动过滤范围内的可见行，即使没有：
Function CountFilterAreaRows(ws As Excel.Worksheet) As Long
Dim FilterArea As Excel.Range
Dim RowsCount As Long
Set ws = ActiveSheet
For Each FilterArea In ws.AutoFilter.Range.SpecialCells(xlCellTypeVisible)
RowsCount = RowsCount + FilterArea.Rows.Count
Next FilterArea
'don't count the header
RowsCount = RowsCount - 1
CountFilterAreaRows = RowsCount
End Function
To call it as a function, see the edits above. Using your example you would could call it something like this (Untested):
要将其作为函数调用，请参阅上面的编辑。使用您的示例，您可以将其称为这样的（未测试）：
Sub UseIt()
Dim ws As Excel.Worksheet
Dim itm
Dim col As Collection
'... your col logic
For Each itm In col
Set ws = ActiveSheet
ActiveSheet.Range("A2:Z2").AutoFilter Field:=6, Criteria1:=itm
Debug.Print CountFilterAreaRows(ws)
Next itm
End Sub
Note that you should avoid the use of Select.
请注意，您应该避免使用Select.
回答by user2140261
I could be wrong, because I am guessing at what your code is actually doing, but see if this gets what you want done.
我可能是错的，因为我在猜测你的代码实际上在做什么，但看看这是否得到了你想要的结果。
For Each itm In Col
RowCount = Sheets("Master").Rows(itm.Row).Count
MsgBox RowCount
Next
回答by Gary's Student
Say we have an AutoFilter with the first row containing headers and nothing below the filtered table:
假设我们有一个自动过滤器，第一行包含标题，过滤表下方没有任何内容：
Sub visiCount()
Dim r As Range, n as Long
n = Cells(Rows.Count, 1).End(xlUp).Row
Set r = Range("A1:A" & n).Cells.SpecialCells(xlCellTypeVisible)
MsgBox r.Count - 1
End Sub
EDIT............started at A1rather than A2
编辑…………从A1而不是A2 开始
