0

I've seen this question on the boards before but haven't been able to get this to work.

I'm trying to filter data out of a column based on a drop down list.

I've been able to get this to work for a single variable but with multiple variables it seems to jump straight to the last one for some reason and I've no idea why.

Dim A1 As Variant,

A1 = Worksheets("Comparison Sheet").Cells(9, 4).Value

Sheets("Data Sheet").Range("B$31:S279").AutoFilter Field:=4, Criteria1:=("<>" & A1), Operator:=xlFilterValues

The above works with the single variable

Dim A1 As Variant, A2 As Variant, A3 As Variant, A4 As Variant

A1 = Worksheets("Comparison Sheet").Cells(9, 4).Value
A2 = Worksheets("Comparison Sheet").Cells(10, 4).Value
A3 = Worksheets("Comparison Sheet").Cells(11, 4).Value
A4 = Worksheets("Comparison Sheet").Cells(12, 4).Value

Sheets("Data Sheet").Range("B$31:S279").AutoFilter Field:=4, Criteria1:=Array("<>" & A1, "<>" & A2, "<>" & A3, "<>" & A4), Operator:=xlFilterValues

This returns a run-time error (1004) "AutoFilter method of Range class failed"

I'm not sure why I'm getting this error here as I've used basically the same code as an inclusive filter and have had no errors or problems with it eg

Sheets("Data Sheet").Range("B$31:S279").AutoFilter Field:=4, Criteria1:=Array(A1, A2, A3, A4), Operator:=xlFilterValues

Lastly, When I set the Operator to xlAnd,

`Sheets("Data Sheet").Range("B$31:S279").AutoFilter Field:=4, Criteria1:=Array("<>" & A1, "<>" & A2, "<>" & A3, "<>" & A4), Operator:=xlAnd`

It will always only show the value in A4 and I'm not sure why that is.

braX
  • 11,506
  • 5
  • 20
  • 33
AesusV
  • 39
  • 6
  • Duplicate question. https://stackoverflow.com/questions/28575754/filter-out-multiple-criteria-using-excel-vba You can only have 2 criteria if you want to do not equal to. – Harry Lee Nov 11 '21 at 03:04
  • 2
    Does this answer your question? [filter out multiple criteria using excel vba](https://stackoverflow.com/questions/28575754/filter-out-multiple-criteria-using-excel-vba) – Harry Lee Nov 11 '21 at 03:05

0 Answers0