Auto Filter in VBA

Question

I want to Auto Filter Column "FP" There are ~200 names and I want to view all except a selected few. i.e. I want to select all in the filter dropdown box except for the names specified.

Sub Button1_Click()


With Worksheets("RawData")

  .AutoFilterMode = False

  .AutoFilter Field:=172, Criteria1:="<>John", Criteria2:="<>Kelly", 
  Criteria4:="<>Don", Criteria5:="<>Chris"

End With

MsgBox (a)

End Sub

Answer 1

Unfortunately, there isn't a built-in way to do this.

Fortunately, however, it's still possible.

I threw this function together that will filter your values. What it does is it takes a list of all your values in the range you want to autofilter, then removes the values you want excluded.

This function returns just the array of values to keep. So, technically you are not filtering by an exclusion list.

Please Note: This has minimal testing

Function filterExclude(filterRng As Range, ParamArray excludeVals() As Variant) As Variant()

    Dim allValsArr() As Variant, newValsArr() As Variant

    Rem: Get all values in your range
    allValsArr = filterRng

    Rem: Remove the excludeVals from the allValsArr
    Dim aVal As Variant, eVal As Variant, i As Long, bNoMatch As Boolean
    ReDim newValsArr(UBound(allValsArr) - UBound(excludeVals(0)) - 1)
    For Each aVal In allValsArr
        bNoMatch = True
        For Each eVal In excludeVals(0)
            If eVal = aVal Then
                bNoMatch = False
                Exit For
            End If
        Next eVal
        If bNoMatch Then
            newValsArr(i) = aVal
            i = i + 1
        End If
    Next aVal

    filterExclude = newValsArr

End Function

Then you would use the above function something like this:

Sub test()

    Dim ws As Worksheet, filterRng As Range
    Set ws = ThisWorkbook.Worksheets(1)
    Set filterRng = ws.UsedRange.Columns("A")

    With filterRng
        .AutoFilter Field:=1, Criteria1:=filterExclude(filterRng, _
                Array("Blah2", "Blah4", "Blah6", "Blah8")), Operator:=xlFilterValues
    End With

End Sub

Your Criteria1 is set equal to the returned array from the filterExclude() function.

---

In your particular case, your code would look like:

Sub Button1_Click()

    Dim ws As Worksheet, filterRng As Range, exclVals() As Variant
    Set ws = ThisWorkbook.Worksheets("RawData")
    Set filterRng = ws.UsedRange.Columns("FP")
    exclVals = Array("John", "Kelly", "Don", "Chris")

    ws.AutoFilterMode = False

    With filterRng

        .AutoFilter Field:=1, Criteria1:=filterExclude(filterRng, exclVals), _
                Operator:=xlFilterValues
    End With

End Sub

as long as you also have the function I provided in a public module.

---

See it Live

Answer 2

You can't choose more than 2 values to filter out. So we build an array with values you want to filter out and then we colour the values. Then we can use conditional formatting to filter all the values that has no colour.

The drawback is that the last part removes all the conditional formatting for the range that the filter was applied on. You could remove that part and manually remove the conditional formatting that contains values from your array.

VBA Code applied on your data:

Sub Button1_Click()

Dim fc As FormatCondition
Dim ary1 As Variant
fcOrig = ActiveSheet.Cells.FormatConditions.Count
ary1 = Array("John", "Kelly", "Don", "Chris")

For Each str1 In ary1
    Set fc = ActiveSheet.Range("FP:FP").FormatConditions.Add(Type:=xlTextString, String:=str1, TextOperator:=xlContains)
    fc.Interior.Color = 16841689
    fc.StopIfTrue = False
Next str1
ActiveSheet.Range("FP:FP").AutoFilter Field:=1, Operator:=xlFilterNoFill
'Last Part of code remove all the conditional formatting for the range set earlier.
Set fc = Nothing
    If fcOrig = 0 Then
    fcOrig = 1
        For fcCount = ActiveSheet.Cells.FormatConditions.Count To fcOrig Step -1
        ActiveSheet.Cells.FormatConditions(fcCount).Delete
        Next fcCount
    Else
        For fcCount = ActiveSheet.Cells.FormatConditions.Count To fcOrig Step -1
        ActiveSheet.Cells.FormatConditions(fcCount).Delete
        Next fcCount
    End If
MsgBox (a)

End Sub