AutoFilter Date...

Hello everybody,

I met many difficult situations in the vba programming world but yesterday I met one of the most unexpected issues. Working with a small excel application, at a moment given I had to filter programatically some cases by date. Surprisingly, although I have used this function many times when I tried to filter all cases which "are not equal" with a certain date I failed....To be more precise, I will display below - in a synthetic manner - my context:
Filtering manually, using Custom Autofilter, I tried to get the different cases by "23/02/1966" and as you can see it worked...
Manualy.png
But my purpose was to get these results programatically. So, writting following common line, which is a simply concatenation, the filter didn't recognize the condition.

Sub FilterTest()
'........code
Cells.AutoFilter Field:=2, Criteria1:="<>" & "23/02/1966"
'........code
End Sub

If we take a look at the table below we will see that although all rows are filtered the condition "does not
equal" with "23/02/1966" is failing each time.
Programatically.png
Simply, the excel seems to treat in a different way "<>" & "23/02/1966" unlike "<>23/02/1966"...Interesting, this difference occurs only for those operators which involve a comparision in terms of order. For the "equal" operator both filtering ways work fine. After one hour of attempts, the simply conversion of the date format to long format made the filter to work properly. So, the following lines seem to be the right approach :

Sub FilterTest()
Dim s As Date
s = "23 / 2 / 1966"
Cells.AutoFilter Field:=2, Criteria1:="<>" & CLng(s)
End Sub

Could someone tell me why these operations are treated in a different way by Excel ? Thank you in advance.


Daniel

Actually "23/2/1966" is a text string, not a date. But even if you use a date it is still necessary to convert the value to a Long for the filter to work:

Please Login or Register  to view this content.

Hello Thalassa,

The s variable is anyway a date variable because it is declared as date...Even it is a string expression, it is automatically recognized as date by VB without any appeal to DataValue function (as long as VarType(s) = 7). If someone wants to be sure that a date is read properly by VB or to avoid some 'unambiguous dates' it can use this function but in my case I considered it would be a redundant detail (I am aware it is not a good practice). The main issue, here, is that "<>23 / 2 / 1966" is not the same thing with "<>" & "<>23 / 2 / 1966". Shortly speaking, 'AB' (where A is an operator and B a date) is different of the 'A' & 'B'...
Thank you for your interests.

Daniel