filter with multiple criteria and partial values

[Tanyaann1995]

Hi,

Currently, I am using the below code for filtering criteria with partial value only:

With Workbooks("Latest hist prices 4.xlsm").Worksheets(2)
        .Range("A1:U79578").AutoFilter Field:=4, Criteria1:="=*" & cname & "*"

Is there a way to filter with multiple criteria but the criteria is just a partial value just like the above? I tried to edit the above code as given below but the data is not getting filtered with the partial value as criteria. The sheet shows blank cells because nothing is getting filtered.

With Workbooks("Latest hist prices 4.xlsm").Worksheets(2)
        .Range("A1:U79578").AutoFilter Field:=4, Criteria1:="=*" & cname1 & "*", Operator:=xlAND, Criteria2:="=*" & cname2 & "*"

 

[Fluff]

You need to use xlOr rather than xlAnd

 

[Tanyaann1995]

Hi,

Currently, I am using the below code for filtering criteria with partial value only:

With Workbooks("Latest hist prices 4.xlsm").Worksheets(2)
        .Range("A1:U79578").AutoFilter Field:=4, Criteria1:="=*" & cname & "*"

Is there a way to filter with multiple criteria but the criteria is just a partial value just like the above? I tried to edit the above code as given below but the data is not getting filtered with the partial value as criteria. The sheet shows blank cells because nothing is getting filtered.

With Workbooks("Latest hist prices 4.xlsm").Worksheets(2)
        .Range("A1:U79578").AutoFilter Field:=4, Criteria1:="=*" & cname1 & "*", Operator:=xlAND, Criteria2:="=*" & cname2 & "*"

Hi,

Thanks, the above worked.
I have a form that asks the user to fill in the customer name that they want to filter against. The user can add either 1 or more than 1 customer. If more than 1 customer is added, the below code works perfectly. But, if there is only one customer name added, then the filtered results include all the customers in the list. Please advise if there is a way to use the below code to filter with just 1 customer if the user adds only 1 customer.

With UserForm1
 .Show
 cname1 = .TextBox1.Value
 cname2 = .TextBox3.Value
 ename = .TextBox2.Value
 End With

With Workbooks("Latest hist prices 4.xlsm").Worksheets(3)
.Range("A1:U79578").AutoFilter Field:=4, Criteria1:="=*" & cname1 & "*", Operator:=xlOr, Criteria2:="=*" & cname2 & "*"
        .Range("A1:U79578").AutoFilter Field:=6, Criteria1:="=*" & ename & "*"

 

[Fluff]

How about

With Workbooks("Latest hist prices 4.xlsm").Worksheets(3)
   If cname2 = "" Then
      .Range("A1:U79578").AutoFilter Field:=4, Criteria1:="=*" & cname1 & "*"
              .Range("A1:U79578").AutoFilter Field:=6, Criteria1:="=*" & ename & "*"
   Else
      .Range("A1:U79578").AutoFilter Field:=4, Criteria1:="=*" & cname1 & "*", Operator:=xlOr, Criteria2:="=*" & cname2 & "*"
              .Range("A1:U79578").AutoFilter Field:=6, Criteria1:="=*" & ename & "*"
   End If
End With

 

[Tanyaann1995]

How about

With Workbooks("Latest hist prices 4.xlsm").Worksheets(3)
   If cname2 = "" Then
      .Range("A1:U79578").AutoFilter Field:=4, Criteria1:="=*" & cname1 & "*"
              .Range("A1:U79578").AutoFilter Field:=6, Criteria1:="=*" & ename & "*"
   Else
      .Range("A1:U79578").AutoFilter Field:=4, Criteria1:="=*" & cname1 & "*", Operator:=xlOr, Criteria2:="=*" & cname2 & "*"
              .Range("A1:U79578").AutoFilter Field:=6, Criteria1:="=*" & ename & "*"
   End If
End With

Thanks, this worked

 

[Fluff]

You're welcome & thanks for the feedback.