CODEWITHSUNDEEP
excelautofiltervba
Joe Wilson
Joe Wilson

Reputation: 71

Using an array for autofilter criteria

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

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.

Upvotes: 4

Views: 2488

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149295

Use

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

Where MyArray is a string Array

Example

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
'

Screenshot

enter image description here

Upvotes: 6

Related Questions