VBA Code to Deselect Particular Item from filter OR Swap to solve

Question

I have a column called Color, and it is filtered, so I can select\deselect desired Colors.

I recorded a macro to find out how to filter the column for all colors except Blue, Green, and Orange.

The only color that was left was red.

The macro created was as follows:

ActiveSheet.Range("$A$1:$B$5").AutoFilter Field:=1, Criteria1:="Red"

The problem with this code is that it will not work for a generic case, because it is including "Red" and excluding everything else, whereas what I need is to exclude Blue, Green, and Orange, and include everything else.

The reason I need a different code is that my color choices can change based on what I get from the database.

For example, if I pull data and create filter automatically, I could have the color Pink in my list, by my code would be broken, because Pink would not be selected, only Red would be selected.

Any tips on fixing this problem? One idea would be to select everything that is un-selected, and de-select everything that is selected (if there is any vba code to do that). With this strategy, I would select Blue, Green, and Orange, then I would run the swap code.

Possible duplicate of [Excel VBA autofilter all but three](https://stackoverflow.com/questions/19497659/excel-vba-autofilter-all-but-three/35120033#35120033) — BigBen, May 03 '18 at 19:08

QHarr · Accepted Answer · 2018-05-03T18:26:46.767

Like this, where you can pass an array of values as the filter criteria. You can extend the array when you want to add more colours. This is a selecting for rather than de-selecting as if you want to exclude certain colours simply don't add them to the list. This does rely on you having all the possible colours covered though. Filtering out only extends to two values I believe.

Option Explicit

Public Sub test()
    Dim arr()
    arr = Array("Red", "Pink")

    ActiveSheet.Range("$A$1:$B$5").AutoFilter
    ActiveSheet.Range("$A$1:$B$5").AutoFilter Field:=1, Criteria1:=arr, _
                      Operator:=xlFilterValues
End Sub

score 0 · Answer 2 · answered May 03 '18 at 19:20

Your data for colors is the 4 cells from A2 through A5. We construct an array containing those values, but omitting Blue, Green, Orange.

We then apply the array as the filter criteria:

Sub Framm()
    Dim c As Collection, i As Long, v As String, arr
    Set c = New Collection

    For i = 2 To 5
        v = Cells(i, 1)
        If v <> "Green" And v <> "Blue" And v <> "Orange" Then
            On Error Resume Next
                c.Add v, CStr(v)
            On Error GoTo 0
        End If
    Next i

    ReDim arr(1 To c.Count)
    For i = 1 To c.Count
        arr(i) = c.Item(i)
     Next i

ActiveSheet.Range("$A$1:$B$5").AutoFilter Field:=1, Criteria1:=(arr), Operator:=xlFilterValues

End Sub

Before:

and after:

VBA Code to Deselect Particular Item from filter OR Swap to solve

2 Answers2