Peter ask me how he can use =
The example below is always working if you want to filter column A for all dates of 15-Aug-2007
Range("A:A").AutoFilter Field:=1, Criteria1:="=08/15/2007", _
Operator:=xlAnd, Criteria2:="<=08/15/2007" ' Use always the US mm/dd/yyyy format
Or if you enter the date in a worksheet cell (B1 in my example) you can do this
Dim TheDate As Date
Dim DateFormatString As String
TheDate = Range("B1").Value
DateFormatString = "mm/dd/yyyy"
Range("A:A").AutoFilter Field:=1, Criteria1:="=" & Format(TheDate, DateFormatString), _
Operator:=xlAnd, Criteria2:="<=" & Format(TheDate, DateFormatString)
--
Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm
"Ron de Bruin" wrote in message ...
OK, this is working on all systems as far as I know
When you use = it will filter on the text and not on the date value
If you use <=, =, <, it will filter on the date value
Because VBA is US we must always use the US date format
Cells.AutoFilter 2, "<=08/20/2007" for dates <= 20 Aug 2007
If we enter for example DMY format
Excel not know that it is a Date(number)
Or
Cells.AutoFilter 2, "<=" & CLng(Date) for dates <= today
Bed time now for me, good night
--
Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm
"Ron de Bruin" wrote in message ...
Got it Peter
Tomorrow I will test a few things on my Dutch system and post back
I see the problem on my Dutch system and not on my US system.
I remember now why I create the Date tab on the add-in <g
See if the filter in my Add-in is working Correct for you
http://www.rondebruin.nl/easyfilter.htm
--
Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm
"Ron de Bruin" wrote in message ...
Can you send me your workbook Peter
Maybe i see the problem
--
Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm
"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 -