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 -