Search code examples
vbaexcelautofilter

VBA Autofilter Using Multiple Criteria

All,

I am trying to filter on multiple criteria within VBA.

However I cannot find a simple way of doing this. The criteria I am selecting will always be constant but greater than 2 therefore I cannot use the or operator.

Code below;

Selection.AutoFilter field:=10, Criteria1:=Array("Fixtures & Fittings", "Furniture & Equipment", "Land & Buildings", "Motor Vehicles", "Plant & Machinery")

My current solution only filters on the last criteria within the array.

Due to the file set up I am unable to insert a formula in another column.

Thanks

Solution

  • You need Operator:=xlFilterValues.

    Selection.AutoFilter field:=10, Operator:=xlFilterValues, Criteria1:=Array("Fixtures & Fittings", "Furniture & Equipment", "Land & Buildings", "Motor Vehicles", "Plant & Machinery")

    If the criteria is constant, I would assume that the filter range is consistent as well. You may wish to move away from using Selection in the near future. See How to avoid using Select in Excel VBA.