vba - 计算行数并过滤，然后使用 VBA 删除过滤后的行

Question

谁能告诉我如何计算 Excel 电子表格中的总行数，然后动态传递值？

我想传递 lastRow 而不是最后一行的值 2691设置过滤器时的变量。然后我想再次计算总行数，然后从电子表格中删除这些行。

With ActiveSheet
    lastRow = .Cells(.Rows.count, "A").End(xlUp).Row
End With

Range("A2").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$P$2691").AutoFilter Field:=3, Criteria1:= _
    "=ABC", Operator:=xlOr, Criteria2:="=XYZ"
Rows("285:285").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp

Answer 1

关于这个问题:

Instead of the value of 2691 for the last row, I want to pass lastRow variable..

具体答案是设置 Range使用 lastRow 进行过滤您在计算范围内的行数时设置的变量，例如:

lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

试试下面的代码 - 每个步骤都有明确的注释。

Option Explicit

Sub Test()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rng As Range

    'set sheet reference
    Set ws = ActiveSheet

    'turn off autofilter
    ws.AutoFilterMode = False

    'get last row
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    'set range to filter
    Set rng = ws.Range("A1:C" & lastRow)

    'set filter
    rng.AutoFilter Field:=3, Criteria1:="=ABC", Operator:=xlOr, Criteria2:="=XYZ"

    'delete visible rows
    rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete

    'show remaining rows by removing autofilter
    ws.AutoFilterMode = False

End Sub