Trouble filtering on two columns with VBA

LAAdams17

Board Regular
Joined
Oct 23, 2009
Messages
66
I'm having trouble getting my VBA to do an OR sort on two columns. The first sort works on Field 11 and the second sort works on Field 3, but if I try to combine them to get both the selected rows from Sort 1 and the selected rows from Sort 2, I get nothing.

Sort 1
*******
ActiveSheet.ListObjects("Table_IT_Data").Range.AutoFilter Field:=11, _
Criteria1:=Array("Smith, Jane", "Frias, Joe", "Adams, Cindy", _
Operator:=xlFilterValues

Sort 2
********
ActiveSheet.ListObjects("Table_IT_Data").Range.AutoFilter Field:=3, _
Criteria1:="=4041", Operator:=xlOr, Criteria2:="=4058"

My attempt at combining the Sort 1 and Sort 2
**************************************************
With ActiveSheet.ListObjects("Table_IT_Data").Range
.AutoFilter Field:=11, _
Criteria1:=Array("Smith, Jane", "Frias, Joe", "Adams, Cindy", _
.AutoFilter Field:=3, _
Criteria1:="=4041", Operator:=xlOr, Criteria2:="=4058"
End With

Any suggestions would be very much appreciated. Thank you!
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,400
Office Version
  1. 365
Platform
  1. Windows
How about:

VBA Code:
With ActiveSheet.ListObjects("Table_IT_Data").Range
    .AutoFilter Field:=11, Criteria1:=Array("Smith, Jane", "Frias, Joe", "Adams, Cindy"), Operator:=xlFilterValues
    .AutoFilter Field:=3, Criteria1:="=4041", Operator:=xlOr, Criteria2:="=4058"
End With

The macro recorder would be your friend here.
 

LAAdams17

Board Regular
Joined
Oct 23, 2009
Messages
66
How about:

VBA Code:
With ActiveSheet.ListObjects("Table_IT_Data").Range
    .AutoFilter Field:=11, Criteria1:=Array("Smith, Jane", "Frias, Joe", "Adams, Cindy"), Operator:=xlFilterValues
    .AutoFilter Field:=3, Criteria1:="=4041", Operator:=xlOr, Criteria2:="=4058"
End With

The macro recorder would be your friend here.
Hey, thank you very much! Appreciated!
 

LAAdams17

Board Regular
Joined
Oct 23, 2009
Messages
66
HOWEVER, the above solution works as an 'AND' condition: the data needs to fit both the two AutoFilter lines to be included.
What I'm looking for is the OR solution: if either of the two AutoFilter lines is true then include the rows.
 
Last edited:

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,400
Office Version
  1. 365
Platform
  1. Windows
If im understanding thats not how autofilters work. They always work as an 'and' for separate columns in the same table. You can have 'or' in the same column but if you want to use two columns then they use combinations. So you can have:

(Column1 = "ABC" or Column1 = "DEF") AND (Column2 = "GHI" or Column2 = "JKL")

but not

(Column1 = "ABC" or Column1 = "DEF") OR (Column2 = "GHI" or Column2 = "JKL")

You could create a formula in a helper column producing a TRUE or FALSE result based on your conditions then filter on that instead.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,130,048
Messages
5,639,769
Members
417,111
Latest member
buyukbang

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