excel - 如果值与数组中的值匹配，则删除列中的单元格

Question

我试图让 VBA 删除 Z 列中的单元格。我找到了一个我一直在玩的代码片段（如下），但是它非常慢。必须有更快的方法来执行此任务。我知道使用.Select会减慢速度，但现在，它可以工作。任何提高速度的想法将不胜感激。此外，添加动态行数来设置范围也很棒。

Sub DeleteValues()          'This works but is really slow
Dim x As Integer
Dim i As Integer
Dim Arr(1 To 7) As String

Arr(1) = "First search item"
Arr(2) = "Second search item"
Arr(3) = "Third search item"
Arr(4) = "Fourth search item"
Arr(5) = "Fifth search item"
Arr(6) = "Sixth search item"
Arr(7) = "Seventh search item"

Range("Z1").Select

    With Application
            .Calculation = xlCalculationManual
            .ScreenUpdating = False

For x = 1 To 70          'for 70 rows
    For i = 1 To 7       'number of items in the Array
        If ActiveCell.Value = Arr(i) Then
            ActiveCell.Delete
        End If
    Next i
    ActiveCell.Offset(1, 0).Select
Next x

            .Calculation = xlCalculationAutomatic
            .ScreenUpdating = True
    End With

End Sub

标签： excelvba