Filter multiple results in Excel 2003 version

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Filter multiple results in Excel 2003 version

Post by Rudi »

Hi,

I need to convert the following filter statement into Excel 2003 format. I do not have Excel 2003 and am not sure the syntax to use for this version.
The Operator (xlFilterValues) is not valid in 2003 as this relates to the new checkbox style filter in the newer versions.
TX

Code: Select all

With Range("A4", rCell(lRow))
    .AutoFilter Field:=rCell.Column, Criteria1:=Array("E(P)", "L(P)", "S(P)"), Operator:=xlFilterValues
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Filter multiple results in Excel 2003 version

Post by HansV »

In Excel 2003 and before, AutoFilter allows you to specify at most two conditions (values to filter on). To apply three or more conditions, you can use Advanced Filter with a criteria range.
Best wishes,
Hans

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Filter multiple results in Excel 2003 version

Post by Rudi »

TX
Is there any chance that i can pass the CriteriaRange to the filter without having values in the sheet acting as the criteria range? The filter is running as a loop on a large sheet and it seems overkill to have to (insert a whole new sheet or) add the filter values to the sheet, reference the range and then delete the sheet again, just to apply a filter. Can I pass the values as strings using some "magic" syntax??

The new line now looks like this:

Code: Select all

With Range("A4", rCell(lRow))
    .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("IJ4:IJ7"), Unique:=False
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Filter multiple results in Excel 2003 version

Post by HansV »

As the name indicates, CriteriaRange has to be a range. You can't specify an array...
Best wishes,
Hans

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Filter multiple results in Excel 2003 version

Post by Rudi »

TX
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.