Autofilter Criteria not filtering when using NOW()

[bony_tony]

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

 

[Guest]

try doing

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

 

[Ron de Bruin]

Try DateSerial

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

 

[bony_tony]

Still doesn't work.. Same problem..

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










- Show quoted text -

 

[Guest]

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

End Sub

worked fine for me.

 

[Peter T]

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

 

[Peter T]

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

 

[Ron de Bruin]

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

 

[Peter T]

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

 

[Guest]

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.

 

[Peter T]

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

 

[Ron de Bruin]

Can you send me your workbook Peter
Maybe i see the problem

 

[Ron de Bruin]

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

 

[Ron de Bruin]

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

 

[Ron de Bruin]

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)

 

[Ron de Bruin]

Or this one for = to find the exact match

Sub Test2()
Dim TheDate As Date

TheDate = Range("B1").Value

Range("A:A").AutoFilter Field:=1, Criteria1:="=" & Month(TheDate) & "/" & Day(TheDate) & "/" & Year(TheDate)

End Sub

 

[Ron de Bruin]

Range("A:A").AutoFilter Field:=1, Criteria1:="=" & Month(TheDate) & "/" & Day(TheDate) & "/" & Year(TheDate)

Not working in every situation
Use the other two examples I posted for =

I stop this thread now because I am tired switching between my computers and talking to myself <g>