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

Give us some examples
--
Gary''s Student - gsnu201001


"Opa Horst" wrote:

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

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.

--
Regards,

OssieMac


"Opa Horst" wrote:

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

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.

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

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.

--
Regards,

OssieMac

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