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?

You cannot avoid a loop but you could write the data into an array and then loop through the array which would be much faster than looping through cells. if you could share the 8 criteria (conditions), I would post an example. — VBasic2008, Apr 07 '21 at 22:13
Just imagine the conditions are A, B, C, D etc...I want to get the logic and idea down first. Thank you so much in advance! — orange123, Apr 08 '21 at 13:53

Tim Williams · Answer 1 · 2021-04-08T18:33:11.630

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

what do the range("a1") and range("a2")s mean? I am confused to why those cells but not D1 or whatnot as we are filtering for column = 4 in your example — orange123, Apr 08 '21 at 13:29
Advanced Filter requires a "criteria range" where the header(s) correspond to the headers in the range to be filtered, and the rows below contain values to use for filtering. The code above creates a criteria range on a separate sheet (codename `filter`). Did you try it out? https://trumpexcel.com/excel-advanced-filter/ https://www.contextures.com/xladvfilter02.html — Tim Williams, Apr 08 '21 at 17:11
Not yet, was confused on how to implement it. So if I were to implement the code I would change the filtercolumn to 24, array values and sheet1 to an existing sheet? Should I just keep the Filter part the same? Sorry I am still relatively new to vba! — orange123, Apr 08 '21 at 17:16
I've updated my example to make it closer to your original post. — Tim Williams, Apr 08 '21 at 18:27
Helpful & instructive +:); fyi you might be interested in my alternative via newer dynamic array reorganization using `Application.Index()` @TimWilliams — T.M., Apr 09 '21 at 18:03

score 2 · Answer 2 · answered Apr 08 '21 at 14:32

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

score 2 · Answer 3 · answered Apr 09 '21 at 18:01

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)

I don't have the dynamic arrays yet, but works fine using Ctrl+Shift+Enter — Tim Williams, Apr 09 '21 at 18:22
Thank you for responding as it helps me to see that it can be handled even before. — T.M., Apr 09 '21 at 18:24
When entered using C+S+E as a regular array formula, the empty lines are all #N/A (since you have to fill the array formula in a range the same size as the input table, as you don't know how many lines - if any - will be excluded) — Tim Williams, Apr 09 '21 at 18:31

Multiple not equal to <> in VBA Autofilter Implementation

3 Answers3

Write Non-Existing