VBA Autofilter date problem

[davidtrickett]

I have a very large worksheet - autofilter is on and data are in range A4-Y45000(and growing!). Headers are on row 4.
Column U contains dates.
I need some code to filter on a selected date, and on or after it.
Everything I have is set up for the UK date format - i.e. today is 05/08/2017.
The date column is formatted to dd-mmm
So if I select say 15th May the cell displays 15-May, and the edit box shows 15/05/2017

In theory this should work:

Selection.AutoFilter Field:=21, Criteria1:=ActiveCell.Value

But what it does is filter out all non-blank cells! It seems that instead of filtering on 15/05/2017 it is trying to do so on the US date format - 05/15/2017, and since there is no such date it returns nothing.

Stupid ****** machine!

This does work:

Application.ScreenUpdating = False
lastrow = Cells(80000, "u").End(xlUp).Row
If ActiveCell.Column <> 21 Then Exit Sub
If ActiveCell.Row < 4 Then Exit Sub
If ActiveCell.Row > lastrow Then Exit Sub
'Now change column format to date serials - e.g. 42870
Range("U4:U" & lastrow).NumberFormat = ("0")

'Data Filter is already active on this worksheet
Selection.AutoFilter Field:=21, Criteria1:=ActiveCell.Value
'Change format back
Range("U4:U" & lastrow).NumberFormat = ("dd-mmm")
Application.ScreenUpdating = True

To filter on or after change the autofilter line to:

Selection.AutoFilter Field:=21, Criteria1:=">=" & ActiveCell.Value

But this is really clumsy & inelegant.

Is there any way of persuading vba to believe that 15/05/2017 means May 15th 2017?

Incidentally I have come across the same problem in other contexts, so any helpful answer will have wider application.

Thanks

 

[Fluff]

Hia try

Criteria1:=">=" & Format(ActiveCell.Value, "dd/mm/yyyy")

 

[davidtrickett]

Thanks Fluff - but I tried this already - same result!
Weirdly when I check the autofilter on the worksheet it alleges that it is filtering "Greater than or equal to "15/06/2017""! But it ain't!
But when I go to the worksheet autofilter dropdown it shows that a custom filter has been applied - "Greater than or equal to "15/06/2017"". If I then change the date to 14/6/2017 it filters correctly.
There's a gremlin in there somewhere...

 

[Fluff]

1) What happens if you change the active cell to 14/6/2017, does that filter correctly?
2) What happens if you put the date in the code (in American format) ie

Criteria1:=">=" & Format("6/15/17", "dd/mm/yyyy")

 

[davidtrickett]

Still no joy.
But I've at last found what works. I recorded the macros for both options, and tried converting the cell value to US format thus:

Sub selectdate()
seldate = Format(ActiveCell.Value, "mm/dd/yyyy")
Selection.AutoFilter Field:=21, Operator:=xlFilterValues, Criteria2:=Array(2, seldate)
End Sub

and

Sub selectfromdate()
seldate = Format(ActiveCell.Value, "mm/dd/yyyy")
Selection.AutoFilter Field:=21, Criteria1:=">=" & seldate, Operator:=xlAnd
End Sub

Now we both know something new!

Thanks.

 

[MARK858]

What happens with the below code when the cell was 15/05/2017 ....

Selection.AutoFilter Field:=21, Criteria1:=">=" & CLng(ActiveCell.Value)

 

[davidtrickett]

Yes - that works as well - thanks Mark.

 

[genpromo]

What happens with the below code when the cell was 15/05/2017 ....

Selection.AutoFilter Field:=21, Criteria1:=">=" & CLng(ActiveCell.Value)

Hello, I try this format CLng(Date) for autofilter but it did not work for filter date on certain cell. Can you help me :
Cell N2 : 08/09/2018 --> format DD MM YYYYY

Auto filter :
Sheets("Sheet1").Range("A1").AutoFilter Field:=1, Criteria1:=Range("N2").Value & CLng(Date), Operator:=xlOr

How can I fix this ?

 

[MARK858]

Sheets("Sheet1").Range("A1").AutoFilter Field:=1, Criteria1:=[COLOR="#FF0000"]Range("N2").Value & CLng(Date), [/COLOR]Operator:=xlOr

Why are you trying to concatenate 2 dates together for criteria1? Perhaps you would be better describing in words what you are trying to do.

 

[genpromo]

I try to copy from range N2 (format date) to another sheet. For information the format is DD/MM/YYYY, finally I find the problem solver :

Sheets("Sheet1").Range("A1").AutoFilter Field:=1, Criteria1:="=" & Format(Range("N2").Value, "dd/mm/yyyy").

Thank you