使用VBA计算过滤的行

Question

问题是 RowCount 在过滤后返回 1 而不是可见行。

RowCount = Cells(Rows.Count, colIndex).End(xlUp).SpecialCells(xlCellTypeVisible).Row

Sub showEightmonth(ws, colName, colIndex)
Dim RowCount As Integer
    ws.Activate
    MsgBox ws.Name
    RowCount = Cells(Rows.Count, colIndex).End(xlUp).Row
    Set Rng = Range(colName & "1:" & colName & RowCount)
    If ws.AutoFilterMode = True Then
    ws.AutoFilter.ShowAllData
    End If
    Rng.Select
    Rng.NumberFormat = "mm/dd/yyyy hh:mm:ss"
    d = Format(DateAdd("m", -8, Date), "mm/dd/yyyy 07:00:00")
    Range(colName & "1").AutoFilter Field:=colIndex, Criteria1:="<" & d
    RowCount = Cells(Rows.Count, colIndex).End(xlUp).SpecialCells(xlCellTypeVisible).Row
    'Delete filtered row if RowCount > 1, as row 1 is the header row
    If RowCount > 1 Then
        delRow = colName & "2:" & colName & RowCount
        ActiveSheet.Range(delRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End If
    If ws.AutoFilterMode = True Then
    ws.AutoFilter.ShowAllData
    End If
End Sub

Answer 1

要计算应用过滤器后可见的行数，您必须执行以下操作：

Sub countNonFiltered()
Dim sht As Worksheet
Dim colIndex As Long
Dim firstrow As Long
Dim RowCount As Long
Set sht = ThisWorkbook.Worksheets("worksheet name")
colIndex = 1    'let's assume you're interested in column A
firstrow = 2    'Let's assume your header is in row 1 and the data starts from row 2
With sht
    RowCount = .Range(.Cells(Rows.Count, 1).End(xlUp), .Cells(firstrow, colIndex)).SpecialCells(xlCellTypeVisible).Count
    Debug.Print RowCount
End With
End Sub

出于演示目的，上面的代码打印了即时窗口中可见行数。

请记住：

Cells(Rows.Count, colIndex).End(xlUp)

是一个仅由一个单元格组成的区域。相反，您需要的是一个由属于应用过滤器后仍然可见的行的所有单元格组成的范围。

另请记住，当您使用包含行或列索引的变量时，应将它们声明为 Long 。