Excel VBA: how to filter out an array

Question

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

Try to record a macro, excluding the `29` manually and see the recorded code. — Vityata, Jan 03 '21 at 11:22
What does *does not work* mean? Which column are you trying to filter by (e.g. `AC`, `BD` or ...)? Posting an image of the data would surely benefit all readers. — VBasic2008, Jan 03 '21 at 12:34
Excel/VBA do not allow to use more the two such excluding criteria. You can do it only using an extra column where to use formulas able to return the same String/Boolean for all involved conditions and then filter according to it. — FaneDuru, Jan 03 '21 at 14:51

Gary's Student · Accepted Answer · 2021-01-03T18:56:24.263

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:

Friendly hint: A worksheet's `.FilterMode` property is read-only due to [MS Help](https://learn.microsoft.com/de-de/office/vba/api/excel.worksheet.filtermode ) — T.M., Jan 03 '21 at 18:22
@T.M. **Thanks**...................I fixed the code................... — Gary's Student, Jan 03 '21 at 18:30

score 1 · Answer 2 · answered Jan 03 '21 at 15:13

1

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 .

answered Jan 03 '21 at 15:13

Axuary

1,497
1
4
20

... or including the three-names array instead of a column range `=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 :-) – T.M. Jan 03 '21 at 19:27

score 0 · Answer 3 · answered Jan 03 '21 at 14:20

0

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:

https://stackoverflow.com/a/28579593/1277402

answered Jan 03 '21 at 14:20

bugdrown

333
2
11

Excel VBA: how to filter out an array

3 Answers3