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

try doing

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

--
Regards,
Tom Ogilvy



"bony_tony" wrote:

> 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
>
>

Try DateSerial

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


--

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


"bony_tony" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> 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
>

Still doesn't work.. Same problem..


On 24 Aug, 15:08, "Ron de Bruin" <rondebr...@kabelfoon.nl> 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" <tony_in_oz1...@yahoo.co.uk> wrote in messagenews:(E-Mail Removed)...
> > 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 -

Sub AAA()
ActiveSheet.Cells.Select
Selection.AutoFilter Field:=Range("Note1").Column, _
Criteria1:="<=" & Now()

End Sub

worked fine for me.

--
Regards,
Tom Ogilvy


"bony_tony" wrote:

> Still doesn't work.. Same problem..
>
>
> On 24 Aug, 15:08, "Ron de Bruin" <rondebr...@kabelfoon.nl> 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" <tony_in_oz1...@yahoo.co.uk> wrote in messagenews:(E-Mail Removed)...
> > > 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 -
>
>
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Still doesn't work.. Same problem..
>
>
> On 24 Aug, 15:08, "Ron de Bruin" <rondebr...@kabelfoon.nl> 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" <tony_in_oz1...@yahoo.co.uk> wrote in
messagenews:(E-Mail Removed)...
> > > 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 -
>
>

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
news:#5n$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Still doesn't work.. Same problem..
> >
> >
> > On 24 Aug, 15:08, "Ron de Bruin" <rondebr...@kabelfoon.nl> 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" <tony_in_oz1...@yahoo.co.uk> wrote in
> messagenews:(E-Mail Removed)...
> > > > 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 -
> >
> >
>
>

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 news:(E-Mail Removed)...
>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
> news:#5n$(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > Still doesn't work.. Same problem..
>> >
>> >
>> > On 24 Aug, 15:08, "Ron de Bruin" <rondebr...@kabelfoon.nl> 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" <tony_in_oz1...@yahoo.co.uk> wrote in
>> messagenews:(E-Mail Removed)...
>> > > > 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 -
>> >
>> >
>>
>>
>
>

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" <(E-Mail Removed)> wrote in message
news:#(E-Mail Removed)...
> 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
news:(E-Mail Removed)...
> >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
> > news:#5n$(E-Mail Removed)...
> >> 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" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> > Still doesn't work.. Same problem..
> >> >
> >> >
> >> > On 24 Aug, 15:08, "Ron de Bruin" <rondebr...@kabelfoon.nl> 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" <tony_in_oz1...@yahoo.co.uk> wrote in
> >> messagenews:(E-Mail Removed)...
> >> > > > 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 -
> >> >
> >> >
> >>
> >>
> >
> >

Being in the US, I don't have the problem. However, in my test, I did change
the format in the key column to dd/mm/yyyy before running the code.

In the past, I have had luck with some other users with this problem with
regional versions (that use dates starting with day then month) by using the
dateserial.

So not sure what to tell the OP if he/she continues to fail and has
accurately porrayed what he/she is trying to do.

--
Regards,
Tom Ogilvy


"Peter T" wrote:

> 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
> news:#5n$(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > Still doesn't work.. Same problem..
> > >
> > >
> > > On 24 Aug, 15:08, "Ron de Bruin" <rondebr...@kabelfoon.nl> 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" <tony_in_oz1...@yahoo.co.uk> wrote in
> > messagenews:(E-Mail Removed)...
> > > > > 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 -
> > >
> > >
> >
> >
>
>
>