1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Filter column exculding array

Discussion in 'VBA Macros' started by Navi, Feb 12, 2019.

  1. Navi

    Navi Member

    Messages:
    32
    Hi Team, need your help here.

    I have a set of data saved as array in macro and I have named the array as cbox.

    currently I need to filter a column with criteria which are not equal to the array value (> Cbox). Am not getting the result . please help.

    Below the current code.
    Code (vb):

    If Not .AutoFilterMode Then
      Set cel = Range("A1").CurrentRegion
      i = Application.WorksheetFunction.Match("Deptt", Range("A1:B10"), 0)
      cel.AutoFilter Field:=i, Criteria1:=Array("*cBox*"), Operator:=xlFilterValues
      End If
     
    Regards,
    Navi
    Last edited by a moderator: Feb 12, 2019
  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,242
    You need array of exact values. (ex: Array("Item1", "Item2", "Item3")) and NOT partial match (i.e.: Array("Item*")).

    You can find sample code in link below. Post #10 has the latest sample file.

    https://chandoo.org/forum/threads/using-userform-to-autofilter-excluding-multiple-values.30484/

    If you need partial match of criteria... You need something like below.

    Code (vb):
    cel.AutoFilter Field:=i, Criteria1:="<>*cBox*", Operator:=xlAnd
    I assume cBox isn't a variable and is string literal.
    Thomas Kuriakose likes this.
  3. Navi

    Navi Member

    Messages:
    32
    Hi Chihiro,
    Thanks for the same. but still its not working. Below provided the entire code which I am using.

    if we have a list of 5 may be a,b c,d e. the selected array may be "C" which is in cbox. now the macro should filter and eliminate values apart from"C" in the sheets.
    Code (vb):

    Sub Filter()
      Dim LR As Long
      Dim cBox As Variant
      Dim cel As Range
      Dim ws As Worksheet
      Dim fname As String
      Dim i As Integer
      Dim carray() As String
      For Each ws In Worksheets 'removing filters
     If ws.AutoFilterMode Then
      ws.AutoFilterMode = False
      End If
      Next ws
      ReDim cBox(0) 'selecting the tick values
     With Sheets("Selection of Dept")
      .AutoFilterMode = False
      LR = .Cells.Find("*", .Cells(Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
      SearchDirection:=xlPrevious).Row
      For Each cel In .Range("B4:B50")
      If Not cel.Value = "" Then
      cBox(UBound(cBox)) = cel.Offset(0, -1).Value
      ReDim Preserve cBox(UBound(cBox) + 7)

      If IsError(Application.Match("*", (cBox), 0)) Then
      MsgBox "Nothing Selected"
      Exit Sub
      End If
      Sheets("Basedata").Select
      ReDim Preserve cBox(UBound(cBox) - 1)
      If Not .AutoFilterMode Then
      Set cel = Range("A1").CurrentRegion
      i = Application.WorksheetFunction.Match("dept", Range("A1:AZ1"), 0)
      cel.AutoFilter Field:=i, Criteria1:="<>*cBox*", Operator:=xlFilterand
      End If

     
    Last edited by a moderator: Feb 15, 2019 at 9:14 AM
  4. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,242
    Your use of "<>*cBox*" is incorrect.
    You are using array variable, as string literal. Which you cannot.

    Upload sample workbook if you need further help.
  5. Navi

    Navi Member

    Messages:
    32
    Hi Chihiro,

    Attached sample file as requested.

    Thanks in advance.

    Navi

    Attached Files:

  6. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,242
    Ok, so in your current code, you tick the items you want included. You want to make it so that it's excluded?

    In that case, you need to reverse the logic of your array construction.
    Ex:
    Code (vb):
            For Each cel In .Range("B4:B50")
                If cel.Value = "" Then
                    cBox(UBound(cBox)) = cel.Offset(0, -1).Value
                    ReDim Preserve cBox(UBound(cBox) + 7)
                End If
            Next cel
  7. Navi

    Navi Member

    Messages:
    32
    thanks this is working perfect:)

Share This Page