Multiple not equal to <> in VBA Autofilter Implementation

Question

So I know that I can use an array like so for autofiltering:

Temporary.Range("$A$1:$AB$" & RowCountTotal).AutoFilter Field:=24, Criteria1:=Array("1","2","3"), _ Operator:=xlFilterValues

At the same time, I know that I can use <> as not equals too, but I am only allowed having 2 <>'s like so:

Temporary.Range("$A$1:$AB$" & RowCountTotal).AutoFilter Field:=24, Criteria1:=Array("<>1","<>2"), _ Operator:=xlFilterValues

When I do 3<>'s I get an error. The issue is, I need to exclude 8 items from filtering effectively using <> 8 times. My dataset is quite large so I can't filter it row by row as that will take too long.

I read other posts about this such as: filter out multiple criteria using excel vba

but they all used for loops and what not, which I can't have for this. After filtering I will need to copy the filtered worksheet into a new one. How should I approach this?

Answer 1

Write Non-Existing

Option Explicit

Sub writeNonExisting()
    
    Const rCount As Long = 10
    Const CriteriaList As String = "A,B,C,D,E,F,G,H"
    
    Dim Criteria() As String: Criteria = Split(CriteriaList, ",")
    
    Dim rg As Range: Set rg = Temporary.Range("A1:AB" & rCount)
    Dim Data As Variant: Data = rg.Value
    Dim cCount As Long: cCount = UBound(Data, 2)
    
    Dim i As Long, j As Long, k As Long
    For i = 1 To rCount
        If IsError(Application.Match(Data(i, 24), Criteria, 0)) Then
            k = k + 1
            For j = 1 To cCount
                Data(k, j) = Data(i, j)
            Next j
        End If
    Next i
    
    With Temporary.Parent.Worksheets.Add
        .Range("A1").Resize(k, cCount).Value = Data
    End With
    
End Sub

Answer 2

You can do this with Advanced Filter if you have a range you can use to place the criteria (you can use a hidden sheet, or a range on the same sheet as the table you're filtering).

Sub Tester()

    Dim wsTemporary As Worksheet, hdr, RowCountTotal As Long, filterColNum As Long
    Dim rngTable As Range, rngFilter As Range, arrExclude, i As Long, arrFilter
    
    Set wsTemporary = ThisWorkbook.Worksheets("temp")  'where your data is
    
    RowCountTotal = wsTemporary.Cells(Rows.Count, 1).End(xlUp).Row
    
    Set rngTable = wsTemporary.Range("$A$1:$AB$" & RowCountTotal) 'range to filter
    filterColNum = 24                                             'filter on this column index
    hdr = rngTable.Cells(1, filterColNum).Value                   'header to filter on
    
    arrExclude = Array(2, 4, 5, 6, 10)          'for example
    ReDim arrFilter(1 To 2, 1 To UBound(arrExclude) + 1)  'for the criteria values
    'build an array for the filter range content
    For i = LBound(arrExclude) To UBound(arrExclude)
        arrFilter(1, i + 1) = hdr
        arrFilter(2, i + 1) = "<>" & arrExclude(i)
    Next i
    'put the criteria table below the data table
    Set rngFilter = wsTemporary.Cells(RowCountTotal + 10, 1) _
                               .Resize(UBound(arrFilter, 1), UBound(arrFilter, 2))
    rngFilter.Value = arrFilter
    
    'filter the table
    rngTable.AdvancedFilter Action:=xlFilterInPlace, _
                            CriteriaRange:=rngFilter, _
                            Unique:=False
    
    rngFilter.Clear 'clean up: remove the criteria table
        
End Sub

Answer 3

Alternative Udf RemoveExceptions()

Using the new dynamic array features of MS 365/Excel 2019+ you could code the following user defined function as base for any dynamic formula input:

Function RemoveExceptions(DataRng As Range, exceptions, Optional LookUpCol As Long = 1)
'Note: Execptions can be as well a range as an array
    With Application
        Dim data: data = DataRng.Value
        Dim crit: crit = DataRng.Columns(LookUpCol)  ' data column to be matched
    '[1]Check positions
        Dim chk: chk = .Transpose(.Match(crit, exceptions, 0))
        Dim i As Long
        For i = LBound(chk) To UBound(chk)
            chk(i) = IIf(IsNumeric(chk(i)), "DELETE", i)
        Next
        chk = Filter(chk, "DELETE", False)      ' validrow positions
    '[2]Remove exceptions from data
        RemoveExceptions = .Transpose(.Index(data, chk, Evaluate("row(1:" & DataRng.Columns.Count & ")")))
    End With
End Function

Possible formula input

You could display the data to be filtered based on exclusion criteria by entering the following formula e.g. in cell A2 of a results sheet

• either via criteria array as 2nd argument (data range as 1st arg, lookup column = 24 as 3rd parameter)

 =RemoveExceptions(temp!A2:AB100;{2;4;6;8};24)

• or via an extra criteria range as 2nd argument

 =RemoveExceptions(temp!A2:AB100;temp!AD2:AD6;24)