VBA Autofilter Using Multiple Criteria

Question

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

Mideast

add a , Operator:=xlFilterValues as third parameter. – Cocker

What do you mean? You can use an array to assign the values to use exactly as @PEH has just said! – Bronnie

@peh thanks I have used this now. Knew it was something basic missing :) – Mideast

Answer 1

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.

Battleship