如何在 Excel VBA 中过滤后获取可见行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40211789/
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
How do I get count of visible rows after filter in Excel VBA
提问by vivek padelkar
In my Excel sheet I am applying a filter and after that I am counting the visible rows.
I used the following code but I'm getting a wrong count. When I have xlCellTypeVisibleit shows "12" records instead of "14" records and visibleTotalvariable shows "0" count.
在我的 Excel 工作表中，我正在应用过滤器，然后计算可见行。我使用了以下代码，但我得到了错误的计数。当我有xlCellTypeVisible它显示“12”条记录而不是“14条”记录并且visibleTotal变量显示“0”条记录时。
Dim ws As Worksheet
Dim rng As Range
Dim visibleTotal As Long
'xlwkbOutput.Sheets("Sheet1")
Set rng = xlwkbOutput.Sheets("Sheet1").Range("A1:T" & lastRow&)
xlwkbOutput.Sheets("Sheet1").AutoFilterMode = False
rng.AutoFilter field:=1, Criteria1:="#N/A"
visibleTotal = Application.WorksheetFunction.Sum(rng.SpecialCells(xlCellTypeVisible))
' print to the immediate window
Debug.Print visibleTotal
回答by RCaetano
Maybe you can make use of SUBTOTALfunction. This function is used very often when you have filtered values. You can adapt your code to:
也许你可以利用SUBTOTAL功能。当您有过滤值时，此功能经常使用。您可以将代码调整为：
' To SUM filtered rows use 9 as argument of SUBTOTAL or to COUNTA use 3
' "- 1" is to exclude the first row, probably the header of your range; otherwise remove it
visibleTotal = Application.WorksheetFunction.Subtotal(9, rng) - 1
SUBTOTAL Arguments List:
SUBTOTAL 参数列表：
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP
For more information about this function you can check the link.
有关此功能的更多信息，您可以查看链接。
HTH ;)
哈 ;)
回答by user3598756
your narrative is about "counting the visible rows"while your code shows a SUM()function
你的叙述是关于“计算可见行数”，而你的代码显示一个SUM()函数
anyhow here's how you can get both numbers, keeping in mind that Autofilter()will always filter headerrow, i.e. the 1st row of the range it's being called upon
无论如何这里是你如何获得这两个数字，请记住这Autofilter()将始终过滤标题行，即它被调用的范围的第一行
Option Explicit
Sub main()
Dim visibleTotal As Long, visibleRows As Long
With xlwkbOutput.Sheets("Sheet1") '<-- reference your worksheet
.AutoFilterMode = False
With .Range("A1:T5") '<-- reference its relevant range
.AutoFilter field:=1, Criteria1:="#N/A" '<-- apply filter: first row (headers) will always be selected
visibleRows = Application.WorksheetFunction.Subtotal(103, .Resize(, 1)) - 1 '<-- count visible rows, excluding headers (always filtered)
If visibleRows > 0 Then visibleTotal = Application.WorksheetFunction.Sum(.Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)) '<-- sum all visible cells, excluding headers (always filtered)
' print to the immediate window
Debug.Print visibleTotal
End With
.AutoFilterMode = False
End With
End Sub
回答by holmicz
I don't know, don't you want to use Count function instead? It is hard to say, but the behaviour seems correct to me. It is hard to say, when we don't have data. Try to apply sum function in worksheet on visible cells, and check what it returns, if it is same as in macro.
不知道，你不是要用Count函数吗？很难说，但这种行为对我来说似乎是正确的。很难说，当我们没有数据时。尝试在可见单元格的工作表中应用 sum 函数，并检查它返回的内容，如果它与宏中的相同。
