excel 使用筛选器对筛选的行进行计数

xdnvmnnf 于 2023-10-22 发布在其他

关注(0)|答案(2)|浏览(120)

问题是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

excel

来源：https://stackoverflow.com/questions/56665100/count-filtered-rows-using-vba

2条答案

按热度按时间

xfb7svmp1#

您可以使用此函数并将过滤后的范围作为参数传递。

Function FilteredRowsCount(rng As Range) As Long
Dim rngArea, lcount As Long
      For Each rngArea In rng.SpecialCells(xlCellTypeVisible).Areas
        lcount = lcount + rngArea.Rows.Count
      Next
    FilteredRowsCount = lcount - 1
End Function

赞(0）回复(0）举报 2023-10-22

xqnpmsa82#

要计算应用过滤器后可见的行，您必须这样做：

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。

赞(0）回复(0）举报 2023-10-22

我来回答

excel 使用筛选器对筛选的行进行计数

2条答案

相关问题

热门标签

最新问答