CODEWITHSUNDEEP
excelvba
Shinaj
Shinaj

Reputation: 105

Exclude values from a column Autofilter

I have an excel sheet with 3 columns as shown attached. I want to set autofilter on column 3 to exclude values from the filter. My values are in the form of text seperated by commas.

I have tried some code as given below with 2 values (CS, IS) as example. But with my code it exludes CS in the first run of loop and then makes it visible in the 2nd run of loop. Here is my code.

Sub filterValues()
    Dim excludeValues As String
    excludeValues = "CS,IS" 'this is example text
    Dim arrValues() As String
    arrValues = Split(excludeValues, ",")

    For i = 1 To UBound(arrValues)
        Range("A1").AutoFilter Field:=3, Criteria1:="<>" & arrValues(i), Operator:=xlAnd
    Next i
    
End Sub

enter image description here

Upvotes: 2

Views: 121

Answers (2)

VBasic2008
VBasic2008

Reputation: 54797

Filter 'Not Equal To' Data

Sub FilterDataValues()
    
    Const EXCLUDE_LIST As String = "CS,IS"
    Const FILTER_COLUMN_INDEX As Long = 3
    
    Dim dictEx As Object: Set dictEx = CreateObject("Scripting.Dictionary")
    dictEx.CompareMode = vbTextCompare
    
    Dim Key
    
    For Each Key In Split(EXCLUDE_LIST, ",")
        dictEx(CStr(Key)) = Empty
    Next Key
    
    Dim dictIn As Object: Set dictIn = CreateObject("Scripting.Dictionary")
    dictIn.CompareMode = vbTextCompare
    
    Dim Data, rCount As Long, r As Long
    
    With ActiveSheet ' improve!
        If .FilterMode Then .ShowAllData
        If .AutoFilterMode Then .AutoFilterMode = False
        With .Range("A1").CurrentRegion
            rCount = .Rows.Count - 1
            If rCount > 0 Then
                With .Columns(FILTER_COLUMN_INDEX).Resize(rCount).Offset(1)
                    If rCount = 1 Then
                        ReDim Data(1 To 1, 1 To 1): Data(1, 1) = .Value
                    Else
                        Data = .Value
                    End If
                End With
                For r = 1 To rCount
                    Key = CStr(Data(r, 1))
                    If Not dictEx.Exists(Key) Then
                        If Not dictIn.Exists(Key) Then
                            dictIn(Key) = Empty
                        End If
                    End If
                Next r
                If dictIn.Count > 0 Then
                    .AutoFilter FILTER_COLUMN_INDEX, dictIn.Keys, xlFilterValues
                'Else ' no includes; do nothing
                End If
            'Else ' no data; do nothing
            End If
        End With
    End With
    
End Sub

Upvotes: 1

Domenic
Domenic

Reputation: 8104

You can use a Dictionary object to collect a unique list of values for which to filter the data, and then filter the data based on the unique list using the xlFilterValues operator.

Option Explicit

Sub filterValues()

    Dim dataRange As Range
    Set dataRange = Range("A1").CurrentRegion

    Dim excludeValues As Variant
    excludeValues = Array("CS", "IS")
    
    Dim filterValuesDictionary As Object
    Set filterValuesDictionary = CreateObject("Scripting.Dictionary")
    
    filterValuesDictionary.comparemode = 1 'TextCompare
    
    Dim i As Long
    With dataRange
        For i = 2 To .Rows.Count
            If IsError(Application.Match(.Cells(i, 3).Value, excludeValues, 0)) Then
                filterValuesDictionary(.Cells(i, 3).Value) = ""
            End If
        Next i
    End With
    
    With ActiveSheet
        If .FilterMode Then .ShowAllData
    End With
    
    Range("A1").AutoFilter Field:=3, Criteria1:=filterValuesDictionary.keys(), Operator:=xlFilterValues
    
End Sub

Upvotes: 1

Related Questions