CODEWITHSUNDEEP
excelvbafilter
Fighter Jet
Fighter Jet

Reputation: 407

Excel VBA: how to filter out an array

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

Upvotes: 1

Views: 8352

Answers (3)

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

If you want to use AutoFilter and exclude several values, create an array that includes only the "good" values. Say we start with:

enter image description here

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:

enter image description here

Upvotes: 3

Axuary
Axuary

Reputation: 1507

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 .

Upvotes: 1

bugdrown
bugdrown

Reputation: 408

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

Upvotes: 0

Related Questions