使 CountIf 函数仅计算区域中的可见单元格（在 application.worksheet函数中）

问：

我每周（包括前 5 周）跟踪每个操作员的缺陷。我从屏幕顶部的数据表开始，我从中获取唯一员工姓名的列表（放在 T 列中）。

然后，我按每个员工姓名筛选列表，以查看他们在 6 周内的缺陷。如果操作员有当前周（第一列）的缺陷（当前周值存储在变量中：weekNumber），那么我将数据向下复制 20 行并粘贴，从粘贴的数据创建一个新表，然后从员工的新粘贴表创建一个数据透视表，并将数据透视表放在下面。

现在，我遇到了麻烦，只能复制和粘贴包含 Week 值 [weekNumber] 的数据。目前，weekNumber = 41。因此，我只想为在第 41 周至少有一个缺陷的员工复制和粘贴数据。但是当我选择 COUNTIF 范围时，它包括过滤/不可见的单元格，因此如果任何员工在第 41 周有缺陷，那么每个员工的缺陷都会被复制和粘贴。

这是我当前的代码：

Sub FilterEASouthEve()
   Dim ws As Worksheet
   Dim tbl As ListObject
   Dim tbl2 As ListObject
   Dim rng As Range, cell As Range, cell2 As Range
   Dim rngT As Range
   Dim rngN As Range
   Dim filteredData As Range
   Dim destRange As Range
   Dim lastRowN As Long
   Dim lastRowT As Long
   Dim filterValue As Variant
   Dim rngWeek6 As Range
   Dim weekNumber As Integer
    
    'gets week number from first sheet to store in variable
    Sheets("All1700Defects").Select
    weekNumber = Range("S22").Value

    'creates table from data on EAS Eve sheet
    Sheets("EA SOUTH - EVE").Select
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("a1", Cells(Range("a1000000").End(xlUp).Row, "Q:Q")), , xlYes).Name = _
        "Table65"
    Range("Table65[#All]").Select
    ActiveSheet.ListObjects("Table65").Name = "EASouthEve"

   ' Set the worksheet and table
   Set ws = ThisWorkbook.Sheets("EA SOUTH - EVE")
   Set tbl = ws.ListObjects("EASouthEve")

   ' Define the range for Col T (unique employee names), which is used as criteria for filtering the table by employee name
   Set rngT = ws.Range("T2:T" & ws.Cells(Rows.Count, "T").End(xlUp).Row)

    'loop thru each cell in Column T, starting at T2
    For Each cell In rngT
    
        'check if the cell is not empty
        If Not IsEmpty(cell) Then
            
            'get value to filter by
            filterValue = cell.Value
    
        
            ' Apply the filter
            tbl.Range.AutoFilter Field:=14, Criteria1:=filterValue, Operator:=xlFilterValues
            
            'set range for Week column
            Set rngWeek6 = ws.Range("I2", Selection.End(xlDown))
         
            ' Determine the destination range
            lastRowN = ws.Cells(Rows.Count, "N").End(xlUp).Row
            Set destRange = ws.Cells(lastRowN + 20, "A")
         
            'determines if employee has any defects in current week
            If Application.WorksheetFunction.CountIf(rngWeek6, weekNumber) > 0 Then
            
                ' Paste the filtered data below the table with 10-row separation
                Range("EASouthEve[[#Headers],[Date]]").Select
                 Range(Selection, Selection.End(xlDown)).Select
                 Range(Selection, Selection.End(xlToRight)).Select
                 Selection.Copy
                destRange.PasteSpecial xlPasteValues
                Application.CutCopyMode = False
                
                Set rng = Selection
                
                Set tbl2 = ActiveSheet.ListObjects.Add(xlSrcRange, rng, , xlYes)
                
                MakePivotTableFromSelection
            End If
        End If
    Next cell
    
    'turn table filters off
    ActiveSheet.ListObjects("EASOUTHEve").Range.AutoFilter Field:=14
    
    Columns("A:A").Select
    Selection.NumberFormat = "m/d/yyyy"
    Columns("O:O").Select
    Selection.NumberFormat = "m/d/yyyy"
    Columns("P:P").Select
    Selection.NumberFormat = "[$-x-systime]h:mm:ss AM/PM"
    Range("A1").Select
    Selection.End(xlDown).Select
  
End Sub

我尝试将我的范围 （rngWeek6） 更改为：

Set rngWeek6 = ws.Range("I2", Selection.End(xlDown)).SpecialCells(xlCellTypeVisible)

但是我收到“运行时错误1004：无法获取WorksheetFunction类的Countif属性”

我是VBA的新手，碰壁了。如果可以的话，请帮忙。