May 06 2024 05:15 PM
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!
May 06 2024 10:08 PM
@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...