How to filter OUT an array through VBA. I mean, I want to exclude arr array from filed 29. arr is a long list. The following snippet does not work.
arr = Array("<>0735", "<>801124", "<>0613","<>0921", "<>1086", "<>0949", "<>0494", "<>0767", "<>0739")
MySheet.Range("AB1").AutoFilter _
Field:=29, _
Criteria1:=arr, _
Operator:=xlFilterValues
If you want to use AutoFilter and exclude several values, create an array that includes only the "good" values. Say we start with:
and we want to exclude the Stooges. We need to create a "Stooge-free" array":
Sub NoStooges()
Dim rng As Range, c As Collection
Dim r As Range, v As String, n As Long
Dim i As Long, arr
Set rng = Range("A2:A20")
Set c = New Collection
For Each r In rng
v = r.Value
If v <> "Larry" And v <> "Moe" And v <> "Curley" Then
On Error Resume Next
c.Add v, CStr(v)
On Error GoTo 0
End If
Next r
n = c.Count
ReDim arr(1 To n)
For i = 1 To n
arr(i) = c.Item(i)
Next i
With ActiveSheet
If .FilterMode Then Cells.AutoFilter
.Range("$A$1:$A$20").AutoFilter
.Range("$A$1:$A$20").AutoFilter Field:=1, Criteria1:=(arr), Operator:=xlFilterValues
End With
End Sub
The array arr and its associated Collection have only three elements {a, b, c} . The code produces:
.FilterMode property is read-only due to [MS Help](learn.microsoft.com/de-de/office/vba/api/… )
Using the FILTER function might get you the functionality you are looking for.
=FILTER(<data>, ISNA(MATCH(<criteria column>, <exclude list>, 0)))
This will filter out all of the rows in where the value in matches something in the .
=FILTER(A2:A20,ISNA(MATCH(A2:A20,{"Curley","Moe","Larry"},0))). Anyway a valid answer for current dynamic versions (MS 365/2019). Would have been nice to add/edit some explanations why this works (at least for newbies :-)Here's an existing SO answer that may help. Based on this response, it sounds like you may need a workaround since vba can't handle multiple criteria like this in a single column:
29manually and see the recorded code.AC,BDor ...)? Posting an image of the data would surely benefit all readers.