使用数组进行自动过滤条件 [英] Using an array for autofilter criteria

问题描述

我有以下代码，将根据列I中的条件删除行：

I have the below code which will delete rows based on criteria in column I:

Sub Strip()
    Dim rng As Range

    With ActiveSheet
        .Columns("I").AutoFilter Field:=1, Criteria1:="=70-79%", VisibleDropDown:=False
        Set rng = .AutoFilter.Range
    End With
    If rng.Columns("I").SpecialCells(xlCellTypeVisible).Count - 1 > 0 Then
        Application.DisplayAlerts = False
        rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).Delete
        Application.DisplayAlerts = True
    End If
    rng.AutoFilter
End Sub

我有关于 100 我以这种方式采取行动的不同标准。我宁愿不必重复这段代码 100 次，任何人都可以告诉我如何以数组的形式编码？我已经尝试过各种方法，但似乎无法让它工作。

I have about 100 different criteria that I want to act on in this way. I'd rather not have to repeat this code 100 times, and so can anyone tell me how to code this in the form of an array? I've tried various methods but can't seem to get it to work.

推荐答案

使用

.Columns("I").AutoFilter Field:=1, Criteria1:=MyArray,  Operator:=xlFilterValues

其中 MyArray 是一个字符串数组

示例

Dim MyArray(1 To 4) As String

MyArray(1) = "This"
MyArray(2) = "is"
MyArray(3) = "an"
MyArray(4) = "array"

'
'~~> Rest of code
'

.Columns("I").AutoFilter Field:=1, Criteria1:=MyArray, Operator:=xlFilterValues

'
'~~> Rest of code
'

截图

这篇关于使用数组进行自动过滤条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！