Code Check

[Miya]

Hi, what is wrong with this code i get application defined object errror


With Sheets("SecuritiesReport").Range("A5")
.AutoFilter Field:=4, Criteria1:="=DOMCORP", Operator:=xlOr, Criteria2:="=TEMP", _
Operator:=xlOr, Criteria3:="=UNDADMIN", Operator:=xlOr, Criteria4:="=RIGHTS", _
Operator:=xlOr, Criteria5:="=SUBMVMNT"
End With

 

[Sandeep Warrier]

Hi

Autofilter can have only 2 criteria

.Autofilter Field:=4, Criteria1:="something", Operator:=xlOr, Criteria2:="Something else"

If you have XL2007 or higher, you can use array's in the criteria, something like....

.AutoFilter Field:=4, Criteria1:=Array("DOMCORP", "TEMP", "UNDADMIN", "RIGHTS", "SUBMVMNT"), Operator:=xlFilterValues

 

[Miya]

I have 2002, so what can i do?

Hi

Autofilter can have only 2 criteria

.Autofilter Field:=4, Criteria1:="something", Operator:=xlOr, Criteria2:="Something else"

If you have XL2007 or higher, you can use array's in the criteria, something like....

.AutoFilter Field:=4, Criteria1:=Array("DOMCORP", "TEMP", "UNDADMIN", "RIGHTS", "SUBMVMNT"), Operator:=xlFilterValues

 

[Sandeep Warrier]

The easiest way would be to have multiple filters..

Example

.Autofilter Field:=4, Criteria1:="DOMCORP", Operator:=xlOr, Criteria2:="TEMP"
'code to do some action
.Autofilter Field:=4, Criteria1:="UNDADMIN", Operator:=xlOr, Criteria2:="RIGHTS"
'code to do some action... etc

This would probably work for you since you seem to have only about 5-6 criteria.

 

[Miya]

That did not work, i require the code to show all those teams in the filter, when i run the code it only captures the second Autofilter and removes the first one

The easiest way would be to have multiple filters..

Example

.Autofilter Field:=4, Criteria1:="DOMCORP", Operator:=xlOr, Criteria2:="TEMP"
'code to do some action
.Autofilter Field:=4, Criteria1:="UNDADMIN", Operator:=xlOr, Criteria2:="RIGHTS"
'code to do some action... etc

This would probably work for you since you seem to have only about 5-6 criteria.

 

[Sandeep Warrier]

It'll be better if you could post some sample data and tell us what exactly you are trying out along with expected results.

 

[Miya]

It'll be better if you could post some sample data and tell us what exactly you are trying out along with expected results.

I have source codes in Col d, i need the code to filter for these criterias if they are there, so i want to see all those teams in the filter.

    With Sheets(1).Range("A5")
            .AutoFilter Field:=4, Criteria1:="DOMCORP", Operator:=xlOr, Criteria2:="TEMP"
            .AutoFilter Field:=4, Criteria1:="UNDADMIN", Operator:=xlOr, Criteria2:="RIGHTS"
        End With

 

[Sandeep Warrier]

If you just want to view them, I don't think it is possible for versions before XL2007.

 

[Miya]

If you just want to view them, I don't think it is possible for versions before XL2007.

There has to be a way with multiple criterias

 

[Sandeep Warrier]

If you are willing to use an extra column, there can be a workaround to this.

In E2 (assuming Column E is empty and data starts from row 2)

=ISNUMBER(MATCH(D2,{"DOMCORP","TEMP","UNDADMIN","RIGHTS","SUBMVMNT"},0))

Copy down.

Now filter on column E for TRUE instead of multiple criteria in column D