VBA on multiple filters basing on multiple cell values

chasoe

Board Regular
Joined
Jan 29, 2006
Messages
64
Dear sirs,

I would like to know why I cannot fix the errors in below codes :

Code:
ActiveSheet.Range("Filter_Range").AutoFilter Field:=1, _
    Criteria1:="=" & Range("X4").Value & """, Operator:=xlOr, _
    Criteria2:="=" & Range("X5").Value & """, Operator:=xlOr, _
    Criteria3:="=" & Range("X5").Value & """, Operator:=xlOr, _
    Criteria4:="=" & Range("X5").Value & """

It seems the error stuck as from line 2.

Could anyone help ?

Thanks in advance.
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,959
Does this work in your macro ?

VBA Code:
Option Explicit

Sub filtrRnge()

ActiveSheet.Range("Filter_Range").AutoFilter Field:=1, _
    Criteria1:="" = "" & Range("X4").Value & """", Operator:=xlOr, _
    Criteria2:="" = "" & Range("X5").Value & """", Operator:=xlOr, _
    Criteria3:="" = "" & Range("X5").Value & """", Operator:=xlOr, _
    Criteria4:="" = "" & Range("X5").Value & """"

End Sub
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,048
Office Version
  1. 2010
Platform
  1. Windows
If you're filtering Field 1 for only 2 things (X4 and X5 values) then I don't know why you have Criteria3 (X5 value) and Criteria4 (X5 value again)

to filter only for X4 and X5 values
VBA Code:
ActiveSheet.Range("Filter_Range").AutoFilter Field:=1, _
    Criteria1:=Range("X4").Value, Operator:=xlOr, _
    Criteria2:=Range("X5").Value, Operator:=xlFilterValues

If you're filtering the field for more than 2 things you need to use an array as the Criteria
Assuming your Criteria3 was meant to be X6 value and Criteria4 meant to be X7 value
VBA Code:
ray = Application.Transpose(Range("X4").Resize(4).Value)
Range("Filter_Range").AutoFilter Field:=1, Criteria1:=ray, Operator:=xlFilterValues
 

chasoe

Board Regular
Joined
Jan 29, 2006
Messages
64
Many thanks for your prompt reply.

In fact, I should apologize for posting the wrong codes as I was so furious testing all possibilities.
The original codes should be as below, which I intended to extend from X4 to X13 (ie more than 2 cells) :
Code:
ActiveSheet.Range("Filter_Range").AutoFilter Field:=1, _
    Criteria1:="" = "" & Range("X4").Value & """", Operator:=xlOr, _
    Criteria2:="" = "" & Range("X5").Value & """", Operator:=xlOr, _
    Criteria3:="" = "" & Range("X6").Value & """", Operator:=xlOr, _
    Criteria4:="" = "" & Range("X7").Value & """"

I've also checked subsequently that the above codes only valid for 2 criteria, and will receive error if more than 3.

And I just tested your codes using array which I don't know before, and they works perfectly.

Thank you indeed.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,444
Messages
5,624,809
Members
416,055
Latest member
EJGAJG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top