CODEWITHSUNDEEP
excelvba
Dharmendra
Dharmendra

Reputation: 129

Filter a Range with values which I don't want

ThisWorkbook.Sheets(1).Range("A1:AR1").AutoFilter Field:=27, _
Criteria1:=Array("<>DRCA", "<>DREX", "<>DRFU", "<>DRIN", _
"<>DRIR", "<>DRND", "<>DRPN", "<>DRPR", "<>DRRE", "<>DRUN", _
"<>REXC", "<>EXCD", "<>RFUR", "<>RINV", "<>RIRC", "<>RNDR", _
"<>RPNA", "<>RPRO", "<>RRET", "<>RUND", "<>RUNF", "<>EXC", "<>C"), _
Operator:=xlFilterValues

This is not returning correct filter data that I want. What I want is that if array doesn't find any <> value then skip it and check next.

How do I go about it?

Upvotes: 1

Views: 646

Answers (2)

Excel Hero
Excel Hero

Reputation: 14764

You cannot use the AutoFilter from VBA to explicitly exclude more than two values for a given field. Instead you need to specify the values you want included.

But you do not need to use the AutoFilter to hide the rows containing specific values.

Here is a short routine that will work in your case. You can manage which rows to hide by editing the first line:

Public Sub Demo()
    Const HIDE = ".DRCA.DREX.DRFU.DRIN.DRIR.DRND.DRPN.DRPR.DRRE.DRUN.REXC.EXCD.RFUR.RINV.RIRC.RNDR.RPNA.RPRO.RRET.RUND.RUNF.EXC.C."
    Dim c As Range
    With ThisWorkbook.Sheets(1)
        For Each c In .Range("AR1:AR" & .Range("AR" & .Rows.Count).End(xlUp).Row)
            If InStr(HIDE, "." & c & ".") Then
                c.EntireRow.Hidden = True
            End If
        Next
    End With
End Sub

Upvotes: 2

Siddharth Rout
Siddharth Rout

Reputation: 149277

Excel doesn't allow you to use the array like you want it in the Autofilter. But is there an alternative? Yes!

Logic If I ask you choose numbers between 0 and 10 including 0 and 10 but you cannot choose 0, 5, 8 and 10. So instead of filtering the numbers out and saying I don't want 0, 5, 8 and 10, you can say I want 1,2,3,4,6,7,9.

Similarly in your case we will not filter on the values we DON'T want. We will filter on values we WANT.

So HOW do we find that list and store it in an array?

  1. Find the last row in the relevant column.
  2. Store all records from that column in a unique collection
  3. Check which items in that collection are not in the "exclude" list and create an array.
  4. Filter the range on the values(array) you want! This way we will not have to filter on the values which we don't want.

Code (Tested in Excel 2013 with 50k records)

I have commented the code but if you still have any questions then feel free to ask :)

Dim OmitArray As Variant
Const deLim As String = "|"

Sub Sample()
    Dim Ws As Worksheet
    Dim lRow As Long, i As Long, n As Long, lCol As Long
    Dim Col As New Collection, itm
    Dim includeArray As Variant
    Dim rng As Range
    Dim tmpString As String

    '~~> This array has all the values that you want to ignore
    OmitArray = Array("DRCA", "DREX", "DRFU", "DRIN", "DRIR", "DRND", _
                "DRPN", "DRPR", "DRRE", "DRUN", "REXC", "EXCD", "RFUR", _
                "RINV", "RIRC", "RNDR", "RPNA", "RPRO", "RRET", "RUND", _
                "RUNF", "EXC", "C")

    '~~> This is the column where you want to filter out
    lCol = 27

    '~~> Change this to the relevant worksheet
    Set Ws = ThisWorkbook.Sheets("Sheet1")

    With Ws
        '~~> Find lastrow
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        '~~> This is the range where the filter will be applied
        Set rng = .Range("A1:AR" & lRow)

        '~~> All all the values from col 27 to a unique collection
        For i = 2 To lRow
            On Error Resume Next
            Col.Add .Cells(i, lCol).Value, CStr(.Cells(i, 27).Value)
            On Error GoTo 0
        Next i

        '~~> Now loop though the collection and store the values in a string
        '~~> delimited with a delimiter which arenot present in the "OmitArray"
        For Each itm In Col
            If Not IsInArray(itm, OmitArray) Then
                If tmpString = "" Then
                    tmpString = itm
                Else
                    tmpString = tmpString & deLim & itm
                End If
            End If
        Next itm

        If tmpString <> "" Then
            '~~> Split the values based on the delimiter to create array
            includeArray = Split(tmpString, deLim)

            '~~> Remove any filters
            .AutoFilterMode = False

            '~~> Filter on the rest of the values
            With rng
              .AutoFilter Field:=lCol, Criteria1:=includeArray, Operator:=xlFilterValues
            End With
        End If
    End With
End Sub

'~~> Function to check if there is an item in the array
Function IsInArray(stringToBeFound As Variant, arr As Variant) As Boolean
    Dim bDimen As Byte, i As Long

    On Error Resume Next
    If IsError(UBound(arr, 2)) Then bDimen = 1 Else bDimen = 2
    On Error GoTo 0

    Select Case bDimen
    Case 1
        On Error Resume Next
        IsInArray = Application.Match(stringToBeFound, arr, 0)
        On Error GoTo 0
    Case 2
        For i = 1 To UBound(arr, 2)
            On Error Resume Next
            IsInArray = Application.Match(stringToBeFound, Application.Index(arr, , i), 0)
            On Error GoTo 0
            If IsInArray = True Then Exit For
        Next
    End Select
End Function

Upvotes: 4

Related Questions