3

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

  • 1
    add a , Operator:=xlFilterValues as third parameter. – Pᴇʜ Jun 6 '18 at 13:27
  • What do you mean? You can use an array to assign the values to use exactly as @PEH has just said! – QHarr Jun 6 '18 at 13:27
  • @peh thanks I have used this now. Knew it was something basic missing :) – SB999 Jun 6 '18 at 14:39
5

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.