Filter column exculding array

[Navi]

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.

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

 

[Chihiro]

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.

cel.AutoFilter Field:=i, Criteria1:="<>*cBox*", Operator:=xlAnd

I assume cBox isn't a variable and is string literal.

 

[Navi]

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.

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

 

[Chihiro]

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.

 

[Navi]

Hi Chihiro,

Attached sample file as requested.

Thanks in advance.

Navi

 

[Chihiro]

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:

        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

 

[Navi]

thanks this is working perfect