VBA:遍历变体的字符串数组，如果值匹配，则删除行

Question

我有多个文本变量(水果(，当它们出现在 R 列上时，我需要删除整行。我有以下代码，但由于我有 15 个"Fruit"变量(将来可能需要添加更多(，我想知道如何使这是一个带有循环的单个子例程。我尝试了很多方法，但都惨遭失败。可能使用包含所有名称的字符串数组？

提前感谢，

Sub Fix1()
Dim Fruit As String
Fruit = "Apple"
Do
On Error GoTo ByeBye
Range("R:R").Find(Fruit).EntireRow.Delete
Loop
ByeBye:
Exit Sub
End Sub

Sub Fix2()
Dim Fruit As String
Fruit = "Orange"
Do
On Error GoTo ByeBye
Range("R:R").Find(Fruit).EntireRow.Delete
Loop
ByeBye:
Exit Sub
End Sub

Answer 1

您可以使用AutoFiter()：

Sub FixAll()
    Dim Fruits As Variant
    Fruits = Array("Apple", "Banana", "Pear") '<--| list your fruits
    With Range("R:R") '<--| reference your range
        .AutoFilter Field:=1, Criteria1:=Fruits, Operator:=xlFilterValues '<--| filter referenced column with 'Fruits'" content
        If Application.WorksheetFunction.Subtotal(103, .Cells) > 1 Then .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete '<--| if any cell found other than header one then delete its corresponding row
    End With
    ActiveSheet.AutoFilterMode = False
End Sub

Answer 2

如果您不需要删除第一行：

Dim r As Range
Set r = ActiveSheet.UsedRange
r.AutoFilter 19 - r.Column, Array("Apple", "Orange"), xlFilterValues 
r.Offset(1).Delete xlShiftUp                               ' deletes the non-filtered rows
r.AutoFilter                                               ' hide the filter