如果单元格包含某些文本，则删除行

I want to perform this functions in this order:
1. Activate auto-filter function
2. In column M, tick 'deposit reversed' and 'blank'
3. Delete entire rows for cell that contain 'AQ*', 'AI*', 'BG' in column C. (NOTE: * represents numbers after the alphabets)

我想用这个顺序来执行这个函数:1。激活的滤清器功能2。在M栏中，勾选“存款转回”和“空白”3。删除c列中包含“AQ*”、“AI*”、“BG”的单元格的整个行(注意:*表示字母后面的数字)

I have tried macro recording using Auto Filter but it only delete rows up to a specified range (which may differ if I use other set of data). VBA as per below.

我尝试过使用自动筛选器进行宏记录，但它只删除指定范围内的行(如果使用其他数据集，可能会有所不同)。VBA如下每。

Is there any EASIER/BETTER way to perform this?

有什么更简单/更好的方法来做这件事吗?

Appreciate your help!

感谢你的帮助!

Sub Macro4()
'
' Macro4 Macro
'

'
Rows("1:1").Select
Application.CutCopyMode = False
Selection.AutoFilter
ActiveSheet.Range("$A$1:$N$46437").AutoFilter Field:=13, Criteria1:= _
    "=Deposit Reversed", Operator:=xlOr, Criteria2:="="
ActiveSheet.Range("$A$1:$N$46437").AutoFilter Field:=3, Criteria1:=Array( _
    "AQ", "AQ01E166N", "AQ01E294N", "AQ01E316N", "AQ01E373N"), Operator:= _
    xlFilterValues
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
ActiveSheet.Range("$A$1:$N$46017").AutoFilter Field:=3, Criteria1:=Array( _
    "AI", "AI04_MMRASHI_TWT", "AI04E230N", "AI04E269N", "AI04E323N"), Operator:= _
    xlFilterValues
ActiveWindow.SmallScroll Down:=-6
Rows("10236:10236").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
ActiveSheet.Range("$A$1:$N$45998").AutoFilter Field:=3, Criteria1:=Array( _
    "BG", "BG01A004", "BG01H082", "BG01H106N"), Operator:=xlFilterValues
ActiveWindow.SmallScroll Down:=-3
Rows("5:5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp

End Sub

2 个解决方案

#1

Put the wildcarded filter criteria into an array and cycle through them. You won't find a faster method of removing the rows and it is safe as long as you check for the existence of rows to delete before deleting them.

将通配符筛选条件放入一个数组中并循环遍历它们。您不会找到一种更快的删除行的方法，而且只要在删除行之前检查要删除的行是否存在，这是安全的。

    Dim c As Long, vCRITC As Variant
    vCRITC = Array("AQ*", "AI*", "BG*")
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    With Sheets("Sheet7").Cells(1, 1).CurrentRegion
        If .AutoFilter Then .AutoFilter
        With .Resize(.Rows.Count, Columns("A:N").Count)
            .AutoFilter Field:=13, Criteria1:="=Deposit Reversed", Operator:=xlOr, Criteria2:="="
            For c = LBound(vCRITC) To UBound(vCRITC)
                .AutoFilter Field:=3, Criteria1:=Chr(61) & vCRITC(c)
                With .Offset(1, 0)
                    If CBool(Application.Subtotal(103, .Columns(3))) Then _
                        .Rows.Delete
                End With
                .AutoFilter Field:=3
            Next c
        End With
        .AutoFilter
    End With
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

It seems there was a typo in your original question that did not include the asterisk wildcard for BG. That may have to do with the composition editor using asterisks to note italics. This first sets the criteria on column M then adds (and removes) each wildcard criteria to column C, deleting rows that it finds.

看来你最初的问题中有一个错误，没有包含BG的星号通配符。这可能与使用星号标注斜体的组合编辑器有关。这首先设置列M上的条件，然后向列C添加(并删除)每个通配符条件，删除它找到的行。

#2

it is not required to use autofilter for such purposes

不需要为此目的使用autofilter

use this

使用这个

Sub test()
Dim i&
i = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
With ActiveSheet
    While i <> 1
        If .Cells(i, "M").Value = "deposit reversed" Or .Cells(i, "M").Value = "" Then
            If UCase(.Cells(i, "C").Value) Like "AQ*" Or _
                UCase(.Cells(i, "C").Value) Like "AI*" Or _
                 UCase(.Cells(i, "C").Value) Like "BG*" Then
                 .Rows(i).Delete
            End If
        End If
        i = i - 1
    Wend
End With
End Sub