EXCEL VBA 反向选择删除包含关键字所在行？

Question

第二个For 语句中，是删除M列包含熊大或者熊二的所在行， 但是实际只想保留“熊大”或者“熊二”的所在行，需要删除其他行。应该怎样修改，谢谢

Sub deleteFKsupplier()
For i = 2 To 4000
If InStr(Range("b" & CStr(i)).Text, "关闭") > 0 Or InStr(Range("b" & CStr(i)).Text, "取消") > 0 Then
Rows(CStr(i) & ":" & CStr(i)).Select
Selection.Delete shift:=xlUp
i = i - 1
End If
Next i
For i = 2 To 4000
If InStr(Range("M" & CStr(i)).Text, "熊大") > 0 Or InStr(Range("M" & CStr(i)).Text, "熊二") > 0 Then
Rows(CStr(i) & ":" & CStr(i)).Select
Selection.Delete shift:=xlUp
i = i - 1
End If
Next i
End Sub

Answer 1

Sub deleteFKsupplier()

    Dim i&, t%, k%, iStr$, arr

    arr = Split("关闭/取消/熊大/熊二", "/") '将该文本按/符号分隔为数组

    For i = 4000 To 2 Step -1 'Step是步长，-1表示每循环次i就减1

        k = 0 '每次循环都重置k

        iStr = Range("B" & i)

        For t = 0 To UBound(arr) '遍历数组arr

            k = k + InStr(iStr, arr(t))

        Next

        If k = 0 Then Rows(i).Delete '不包含数组arr的任意一项则删除第i行

    Next

    MsgBox "处理完毕", 64

End Sub

追问

关闭/取消/熊大/熊二，这四个关键字分在两列中，新代码施行效率高，但是不能全部删除， 关闭和取消在B列， 熊大和熊二在M列

Answer 2

删除M列，不包含 熊大或熊二的行
Sub 删除()
Dim I
For I = Range("M65536").End(xlUp).Row To 2 Step -1
X = InStr(Cells(I, "M"), "熊大")
Y = InStr(Cells(I, "M"), "熊二")
If X = 0 And Y = 0 Then
Rows(I).Delete
End If
Next
End Sub

Answer 3

If InStr(Range("M" & CStr(i)).Text, "熊大") > 0 Or InStr(Range("M" & CStr(i)).Text, "熊二") > 0 Then
修改为
If InStr(Range("M" & CStr(i)).Text, "熊大") = 0 And InStr(Range("M" & CStr(i)).Text, "熊二") = 0 Then

Answer 4

速度的话这个应该是最快的，不过不知道你是只有"熊大"还是包含"熊大"还有别的，如果有别的话，筛选条件要改一下
Sub test10()
With Range("A1")
.AutoFilter Field:=13, Criteria1:="熊大", Operator:=xlOr, Criteria2:="熊二"
.AutoFilter Field:=2, Criteria1:="关闭", Operator:=xlOr, Criteria2:="取消"
.CurrentRegion.Copy
End With
ActiveSheet.AutoFilterMode = False
Worksheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub