OK, this seems to work for me, irrespective of the numberformat in my cells

Selection.AutoFilter Field:=Range("Note1").Column, _
Criteria1:="<=" & Format(Now(), "m/d/yy")


Using XL2000 with default system date format "dd-mm-yy"

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
Hi Ron,

Something strange going on for me, perhaps same as for the OP (I'm using
XL2000).

Maybe this is not correct
Range("Note1").Column

This is OK, I named B1 "Note1" and put dates in B2 down

As I mentioned neither yours nor Tom's work for me. The code I posted
previously does work for me but only if the dates in cells are formatted
with default date format, for me that's International "dd-mm-yy"

If I change numberformat in the date cells to say "dd-mm-yyyy" or
"dd-mmm-yy" I cannot get anything to work correctly.

.NumberFormat returns "m/d/yy"
.NumberFormatLocal returns "dd-mm-yy"

Referring to the code I posted previously, I need to use NumberFormat. The
filter gives wrong results if I use NumberFormatLocal


I put today's date in the middle of the dates.
I recorded a macro to
Filter Custom -
'is less than or equal to'
and pointed to a cell with today's date

The filter worked as expected, ie manually and recording.

I replayed the macro - just like the OP - no rows in the filtered list at
all !

Here's the recorded macro -

Selection.AutoFilter Field:=2, Criteria1:="<=24-08-2007", Operator:=xlAnd

Unless I'm missing something, the only way I see for the OP and me to get
this working reliably would be to:
- trap the cells' numberformat
- clear the cells' numberformat
- filter the date as a long or double number
- reapply the original cells' date numberformat

Regards,
Peter T



"Ron de Bruin" wrote in message
...
If they are dates this is working OK here for column A
And Tom's example will also work.

Columns("A").AutoFilter Field:=1, Criteria1:="<=" &
DateSerial(Year(Date),
Month(Date), Day(Date))

Maybe this is not correct
Range("Note1").Column


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Peter T" <peter_t@discussions wrote in message
...
I jumped in a bit too quick without fully testing, ignore that. There's
a
bit more to it!

Ron & Tom, neither of yours work for me either.

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
Another attempt -

Sub test()
Dim s As String

' asssumes all cells below the header have same date format
s = Range("Note1").Offset(1).NumberFormat

Selection.AutoFilter Field:=Range("Note1").Column, _
Criteria1:="<=" & Format(Now(), s)
End Sub

Regards,
Peter T

"bony_tony" wrote in message
ups.com...
Still doesn't work.. Same problem..


On 24 Aug, 15:08, "Ron de Bruin" wrote:
Try DateSerial

Selection.AutoFilter Field:=Range("Note1").Column,
Criteria1:="<="
&
DateSerial(Year(Date), Month(Date), Day(Date))

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



"bony_tony" wrote in
oglegroups.com...
Hi,
I have this line of code.

Selection.AutoFilter Field:=Range("Note1").Column,
Criteria1:="<=" &
Format(Now(), "dd/mm/yyyy")

I want to filter anything dated today or prior.

It's not working - every line is being filtered out. When I
drop
down
the autofilter button for the column, and go to 'Custom' I can
see
the
correct date is in the dialog box, and the filter works when I
press
OK through it.

Anyone know why this isn't working?

Cheers
Tony- Hide quoted text -

- Show quoted text -