3

I have this simple macro that filters rows based on value in A13 cell. It works fine.

 With ActiveSheet

    .Range("B2:F5000").AutoFilter Field:=2, Criteria1:=.Range("A13")

End With

But I need more values to be applied for this filter, specifically based on two or more cells. So I run this macro:

With ActiveSheet

    .Range("B2:F5000").AutoFilter Field:=2, Criteria1:=.Range("A13:A14:A15")

End With

But it filters only values based on A15 cell. Why is that? I have read all the topics here, but no solution to this specific problem. Thank you all for your help. Libor.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Lajbror
  • 45
  • 1
  • 5
  • 2
    Possible duplicate of [VBA Autofilter Using Multiple Criteria](https://stackoverflow.com/questions/50721630/vba-autofilter-using-multiple-criteria) – Pᴇʜ Jul 06 '18 at 07:13
  • Try with: .Range("B2:F5000").AutoFilter Field:=2, Criteria1:=Array(.Range("A13"), .Range("A14"), .Range("A15")) –  Jul 06 '18 at 08:01
  • IRENE G - It gives me an error. It doesn't work for me or I just don't know how to create such a macro. I uploaded sample sheet to my dropbox so you can download it and test it. [link](dropbox.com/s/wdjn1ouypuc0i4w/Sample.xlsm?dl=0) In that sample sheet you can clearly see its behavior. Thank you very much :-) – Lajbror Jul 06 '18 at 08:08

2 Answers2

2
  1. Remove the old AutoFilter first
  2. Filter on Field:=1. If your range starts in column B and you want to filter in column B then this is the first field not the second.
  3. If you want to filter on values/numbers not text, filter by using a formula instead of the value. Eg =200 to filter for number 200.

Here is an Example that should work.

With ActiveSheet 'better reference a sheet by its name like: Worksheets("Sheet1")
    If .AutoFilterMode = True Then .AutoFilterMode = False 'remove old autofilter
    .Range("B:F").AutoFilter Field:=1, Operator:=xlFilterValues, _ 
       Criteria1:=Array("=" & .Range("A13").Value, "=" & .Range("A14").Value, "=" & .Range("A15").Value)
End With
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
0

Try with:

.Range("B2:F5000").AutoFilter Field:=2, Criteria1:=Array(Range("A13"), Range("A14"), Range("A15"))
Majid Hajibaba
  • 3,105
  • 6
  • 23
  • 55
Jumong
  • 1