Advanced Filtering

[Opa Horst]

Has anyone encountered any issues with range.advanced filtering? I filter on
dates but the function doesn’t filter correctly, it returns to many dates.
Works perfectly when done manually via the Advanced option in the ribbon.

 

[Gary''s Student]

Give us some examples

 

[OssieMac]

In addition to Gary''s Student's request, you say "Works perfectly when done
manually via the Advanced option in the ribbon". Can you post recorded code
of doing this manually.

 

[Opa Horst]

Thanks for your reply; some details:
Data: Criteria: Result manual: Result with vba:
UsageDate UsageDate UsageDate UsageDate
1/01/2009 =5/01/2009 5/01/2009 2/01/2009
1/01/2009 =6/01/2009 6/01/2009 3/01/2009
2/01/2009 4/01/2009
3/01/2009 5/01/2009
4/01/2009 6/01/2009
5/01/2009 6/01/2009
6/01/2009
6/01/2009

Manual process:
Click Advanced on Data ribbon, specify data and criteria range

VBA:
Range(“Dataâ€).AdvancedFilter _
Action:=xlFilterCopy _
, CriteriaRange:=CriteriaRange _
, CopyToRange:=Range(“Resultsâ€) _
, Unique:=False

The data and criteria ranges are large; about 500,000 data rows and 260
Criteria rows.

 

[Opa Horst]

Correction: There are two dates 6/01/2009 in the Manual result.

 

[OssieMac]

Hi Opa Horst,

This is an idiosyncrasy of Excel VBA. It does not recognise d/m/y dates as
Filter Criteria with AutoFilter or Advanced Filter. VBA sees the dates as
m/d/y format.

The options below are as you enter them in the formula bar; not necessarily
as they appear in the cell. Only applies to the Filter Criteria; not the main
data.

You can enter them as one of the following
="=1/5/2009"
(m/d/y format) However, then they will not work in the interactive mode.

="=" & DATEVALUE("5/01/2009")
(d/m/y format) However appears as numeric in cell; not as date format.

="=5 Jan 2009"
This is my preference so that everyone knows what it means and it displays
the correct date (in a format that we understand) in the criteria cell. VBA
has no problems when the literal is used for the month.

Just as a little extra. If you were to record code for AutoFiltering dates,
the recorded code shows the dates in d/m/y format but the code will not run
and repeat the filtering. Editing the code and changing to dd mmm yyyy format
fixes the problem.

 

[Opa Horst]

Thanks OssieMac,

This works great, you made my day!
The range and criteria are on a temporarily sheet which is deleted after the
filtering, only the result is copied to an other sheet, so I opted for
="m/d/yyyy"
For your information: The dates for the criteria are generated from
information the user enters. They specify a year, months and week days, so
its easy to create the dates in the correct format.
Thanks again,
Opa