Hi Tom,

If I had UK regional settings (i.e. with a dd/mm/yy) format, I was able to
reproduce the results reported by Nigel. adiding the explicit Clng
conversion resolved the problem - for me!

If I used US regional settings (with conventional mm/dd/yy dates), your code
worked for me without alteration.

The point of difference between my suggestion and the resoltion adopted by
Nigel, is that the use of the Clng conversion worked whichever of the two
regional settings I employed.

As you correctly surmise, I am using a UK version and I cannot comment for
other versions.


---
Regards,
Norman



"Tom Ogilvy" wrote in message
...
Norman,
Just for clarification (I don't have this problem or have a way to test
it),
you must include the clng - just the dateserial doesn't work?
( as I recall you are using a Non-US version of Excel)
--
Regards,
Tom Ogilvy

"Norman Jones" wrote in message
...
Hi Nigel,

The following version worked for me:

Sub Tester()

Range("A4:C4").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, _
Criteria1:="=" & CLng(DateSerial(2005, 10, 19)), _
Operator:=xlAnd, _
Criteria2:="<=" & CLng(DateSerial(2005, 11, 15))
End Sub


---
Regards,
Norman



"Nigel" wrote in message
...
Hi Tom
I have continued to investigate. It is definitely the date format that
is
causing the problem. If I set the dates on the worksheet to US format
and
set the filter conditional to the same it works. So it appears that
running the filter manually Excel will resolve the date based on local
settings. But VBA uses the US form of dates so when that is applied,
the
sheet is has an incompatible format. Similar problem to userform
control
of
dates.
My problem remains, as I cannot figure out the conversion process, it
seems
VBA always uses US format dates. Any ideas as a workaround?

--
Cheers
Nigel



"Tom Ogilvy" wrote in message
...
try

Range("A4:C4").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="=" &
DateSerial(2005,10,19),
_
Operator:=xlAnd, Criteria2:="=" & DateSerial(2005,11,15)

--
Regards,
Tom Ogilvy

"Nigel" wrote in message
...
Hi All
I am using an autofilter under VBA control. To set up the customer
conditions I record the macro and adapt the code as required.

Manually the filter works ok, and the resulting code is created and
the
filter acts immediately on the datalist showing only those meeting
the
criteria specified. The filter relate to some date ranges shown on
the
sheet
in European format.

If I remove the filter and run the recorded macro, the filter is put
in
place, the Custom conditions are set BUT all rows are filtered. If
I
open
the filter to check the criteria settings, they are as requested by
the
macro. If I then click OK on the filter the resultant list is
filtered
correctly with conditions being applied correctly !!

I suspect it is to do with the DATES - but cannot reconcile the
difference
between the Manual application and the VBA macro? Thoughts anyone?

Using Xl97 (v8) as the target application, but I get the same in
xlXP
(v10).

Recorded (unedited code below)

Range("A4:C4").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="=19/10/2005",
Operator:=xlAnd
_
, Criteria2:="=15/11/2005"


--
Cheers
Nigel