VBA - 2010 - How to invert a filter

Question

I am wondering about a simple solution for reversing the filtered values. Although it seems to me to be an easy task, I have not had a success while researching on the internet.

The situation and problem: I have a table with multiple columns, and lot of rows (exact amount does not matter obviously) and I want to see what was not filtered in exactly one column. The problem is that I normally need to do a lot of clicking

For example - in the database of projects I have filtered the ones worth over 500 000 €, which are mine and are coming from a specific country. By one click I would like to see which ones are below 500 000 €) but are still mine and coming from a specific country.

Possible solutions which came to my mind:

• Create a unique list of what is filtered, unfiltered, and create an unique list of full column, AdvanceFilter by the difference. (That's my white horse - it might work in my opinion)
• Go through each filtering options and check/uncheck one by one.
• Screenshot filters, transfer to text, create a unique values at the column, invert the filter in advanced filtering (very crazy idea, came out of desperation)
• Somewhere easily take a list of what's filtered and inverted it by easy function (that was my initial thought but not working!)

Does anybody has an idea how to approach this situation? I am able to try the VBA on my own so I would be happy if you can point me in the right direction. Of course I would welcome your thoughts in code too.

Answer 1

Here's an idea to toggle a numeric filter. It won't work with all numeric filters, but most of them. For instance, it won't work with Between, because that uses Criteria1 and Criteria2. But you could expand the code to account for that.

Also, it only really works on numeric filters. It will work on some text filters, but only if one criteria is applied.

Sub InvertNumericFilter()

    Dim lFilter As Long
    Dim lo As ListObject
    Dim fltr As Filter
    Dim aOper As Variant, aOpp As Variant
    Dim i As Long

    'aOpp is the opposite of the corresponding
    'operator in aOper
    aOper = Split("<> <= >= = < >")
    aOpp = Split("= > < <> >= <=")

    'Find which column you're in
    Set lo = ActiveCell.ListObject
    lFilter = ActiveCell.Column - lo.DataBodyRange.Column + 1
    Set fltr = lo.AutoFilter.Filters(lFilter)

    'if the first characters of the criteria are in aOper
    'then swap them for aOpp
    For i = LBound(aOper) To UBound(aOper)
        If Left(fltr.Criteria1, Len(aOper(i))) = aOper(i) Then
            lo.DataBodyRange.AutoFilter lFilter, Replace$(fltr.Criteria1, aOper(i), aOpp(i))
            Exit For
        End If
    Next i

End Sub

Your example happened to be inverting a number, but if you want it to be universal (apply to nonnumerics), it would get a lot more complicated.

Update

This will invert value lists, but it makes some assumptions. For one, if you only have two values, it's not a value list, it's an xlOr operator. If you're using xlOr on some other type of field, it might cause problems.

Sub InvertFilter()

    Dim lFilter As Long
    Dim lo As ListObject
    Dim fltr As Filter
    Dim aOper As Variant, aOpp As Variant
    Dim i As Long, j As Long
    Dim dc As Scripting.Dictionary
    Dim vaValues As Variant

    'Find which column you're in
    Set lo = ActiveCell.ListObject
    lFilter = ActiveCell.Column - lo.DataBodyRange.Column + 1
    Set fltr = lo.AutoFilter.Filters(lFilter)

    'lists of values or just two values
    If fltr.Operator = xlFilterValues Or fltr.Operator = xlOr Then
        'get all the possible values and put in a dictionary
        vaValues = lo.ListColumns(lFilter).DataBodyRange.Value
        Set dc = New Scripting.Dictionary
        For i = LBound(vaValues, 1) To UBound(vaValues, 1)
            If Not dc.Exists("=" & vaValues(i, 1)) Then
                dc.Add "=" & vaValues(i, 1), "=" & vaValues(i, 1)
            End If
        Next i

        'If it's more than two values
        If IsArray(fltr.Criteria1) Then
            'remove from dictionary
            For i = LBound(fltr.Criteria1) To UBound(fltr.Criteria1)
                If dc.Exists(fltr.Criteria1(i)) Then
                    dc.Remove fltr.Criteria1(i)
                End If
            Next i
        Else
            dc.Remove fltr.Criteria1
            dc.Remove fltr.Criteria2
        End If
        'reapply filter
        lo.DataBodyRange.AutoFilter lFilter, dc.Keys, xlFilterValues
    ElseIf fltr.Operator = 0 Then

        'aOpp is the opposite of the corresponding
        'operator in aOper
        aOper = Split("<> <= >= = < >")
        aOpp = Split("= > < <> >= <=")

        'if the first characters of the criteria are in aOper
        'then swap them for aOpp
        For i = LBound(aOper) To UBound(aOper)
            If Left(fltr.Criteria1, Len(aOper(i))) = aOper(i) Then
                lo.DataBodyRange.AutoFilter lFilter, Replace$(fltr.Criteria1, aOper(i), aOpp(i))
                Exit For
            End If
        Next i
    End If

End Sub