code to filter data

Copper Contributor

I have written code to filter an array of data:

ActiveSheet.Range("$A$1:$K$10000").AutoFilter Field:=3, Criteria1:=Array("3340", "3341", "3345", "3347", "3349", "3350", "3353", "3360", "3361", "3363", "S5D1", "S5D7", "S5D9", _
"S5E3", "S5E4", "S5E8", "S5E9", "S5H1", "S5Q2", "S5Q3"), Operator:=xlFilterValues

 

How can I make the filter leave only the values in the array and delete all rows that are not in the array?  

 

Thank you in advance!

1 Reply

@Kraway If you're absolutely sure you want to delete all rows that don't meet the criteria, without keeping a copy of the original data, the following code should do the trick:

 

Option Explicit

Sub DeleteOtherRecords()

    Dim rg As Range, arr As Variant, i As Long
    Set rg = ActiveSheet.Range("A1").CurrentRegion
    arr = Array("3340", "3341", "3345", "3347", "3349", "3350", "3353", "3360", "3361", "3363", _
        "S5D1", "S5D7", "S5D9", "S5E3", "S5E4", "S5E8", "S5E9", "S5H1", "S5Q2", "S5Q3")

    Application.ScreenUpdating = False
    For i = rg.Rows.Count To 2 Step -1
        If UBound(Filter(arr, rg.Cells(i, 3).Value)) = -1 Then rg.Rows(i).Delete
    Next i
    Application.ScreenUpdating = True

End Sub

 

However, if you are using Excel for MS365, this could also be achieved with worksheet formulas:

 

=FILTER(Sheet1!A2:E10000, ISNUMBER(XMATCH(Sheet1!C2:C10000, TEXTSPLIT("3340,3341,3345,3347,3349,3350,3353,3360,3361,3363,S5D1,S5D7,S5D9,S5E3,S5E4,S5E8,S5E9,S5H1,S5Q2,S5Q3",","))))

 

See attached...