在excelvba中应用filter后，对所选单元格范围进行计数

Question

感谢您查看我的情况：）在应用筛选器后，我尝试只计算特定列中选定的行，结果非常糟糕。在应用过滤器后，我正在为一列计数所选的行&它报告所有行。过滤后的行为21行，但似乎包含了所有行。我只想在筛选后对所选行进行计数。

Sub Report()
    Dim sh As Worksheet
    Dim k As Long
    Set sh = ThisWorkbook.Sheets("Testing")
    sh.Range("F21").Activate
    ActiveSheet.Range("F" & Rows.Count).End(xlUp).AutoFilter Field:=6, Criteria1:="Fatal"
    ActiveSheet.Range("f21", ActiveSheet.Range("f21").End(xlDown)).Select
    MsgBox ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Rows.Count
    'FatCnt = Range("F21").Rows.SpecialCells(xlCellTypeVisible).Count
    'FatCnt = Selection.Count
    'MsgBox FatCnt    
    Selection.AutoFilter
End Sub

Answer 1

在单元格中使用公式来获得"致命"计数

=COUNTIF(F:F,"Fatal")

或

Sub Report()
    With ThisWorkbook.Worksheets("Testing")
        fatalcount = WorksheetFunction.CountIf(Range("F:F"), "Fatal")
    End With
End Sub

Answer 2

工作表的本机SUBTOTAL函数在计算过滤单元格方面做得很好。

Sub Report()
    Dim k As Long
    With ThisWorkbook.Worksheets("Testing")
        If .AutoFilterMode Then .AutoFilterMode = False
        With .Range(.Cells(21, "F"), .Cells(Rows.Count, "F").End(xlUp))
            .AutoFilter Field:=1, Criteria1:="Fatal"
            k = Application.Subtotal(103, .Cells) - 1 'minus 1 for the header
            Debug.Print k
        End With
        If .AutoFilterMode Then .AutoFilterMode = False
    End With
End Sub