0

I have been looking for a code to filter criteria out of a list I do not want. I have a code that works for filtering criteria I do want but when I try to reverse it, I get a "mismatch" error.

Here is my code for filtering out criteria I do not want. 

Sub Filters()              
    Dim IntP As Worksheet 'sheet where the main table is
    Dim Param As Worksheet 'sheet where my parameters are
    Dim iRange As Range 'the range of my table
    Dim range1 As Range 'the range that contains the list I want to filter in iRange

    Set IntP = Worksheets("Internet Promotions")
    Set Param = Worksheets("Sheet1")
    Set iRange = IntP.Range("A1", ("AU" & IntP.Range("A" & Rows.Count).End(xlUp).Row)) 'range of my table
    Set range1 = Param.Range("D2", ("D" & Param.Range("D" & Rows.Count).End(xlUp).Row)) 'range of my paramters

    Dim var1 As Variant
    Dim sArray() As String
    Dim i As Long

    '---------------Filter-----------------------   
    var1 = range1.Value
    ReDim sArray(1 To UBound(var1))

    For i = 1 To (UBound(var1))
        sArray(i) = var1(i, 1)
    Next

    iRange.AutoFilter Field:=21, Criteria1:="<>" & sArray, Operator:=xlFilterValues  
End Sub

I do not understand why this is not working.

Any help would be greatly appreciated.

braX
  • 11,506
  • 5
  • 20
  • 33
GCC
  • 285
  • 6
  • 23
  • 1
    I'm SURE you can find the very solution of this question in SO: just spend some more time – DisplayName Dec 10 '18 at 19:39
  • The mismatch might be that you are putting an array where a string is expected. – HackSlash Dec 10 '18 at 20:51
  • Possible duplicate of [filter out multiple criteria using excel vba](https://stackoverflow.com/questions/28575754/filter-out-multiple-criteria-using-excel-vba) – Pᴇʜ Dec 11 '18 at 08:33

1 Answers1

0

Try to filter your data with several criterion on many fields.

See the following example.

enter image description here

Sub filter_col()

'Apply filters on two columns

With ActiveSheet.Range("B3:D6")
    .AutoFilter field:=2, Criteria1:="New-York"
    .AutoFilter field:=3, Criteria1:=">1300000"
End With

End Sub

enter image description here

Alex Yahiaoui Martinez
  • 909
  • 1
  • 9
  • 21
  • The question was not about how to filter a range by *many fields* and *one* criteria. The question is about how to **remove/filter out** (negative filter) multiple values from **one** field. – Pᴇʜ Dec 11 '18 at 08:30