VBA Date filter not working

[JackDanIce]

Hi,

Part of some code is following:

dataRng.AutoFilter field:=10, Criteria1:=">" & xxxx

I want to test for dates greater than 30 June 2019, however, no matter how I replace xxxx, after the code runs, the filtered values are hidden.

If I then select the filter drop down arrow, check the applied filter and hit ok, then then updates and shows the right results.

I've tried:

dataRng.AutoFilter field:=10, Criteria1:=">" & #30/06/2019#
dataRng.AutoFilter field:=10, Criteria1:=">" & DateSerial(Year(Date) + IIf(Month(Date) > 6, 1, 0), 6, 30)

But all have the same behaviour, filtered results not returned.

dataRng is a range that currently evaluates to address A19:K56

This however runs fine:

dataRng.AutoFilter Field:=10, Criteria1:="<" & CDbl(Date + 7)

to show rows where date values are within next 7 days


J19:J56 has blank cells or dates, formatting and hidden chars have been checked for.

Recorded macro returns:

Sub Macro1()
'
' Macro1 Macro
'
    ActiveSheet.Range("$A$19:$K$56").AutoFilter Field:=10, Criteria1:= _
        ">30/06/2019", Operator:=xlAnd
End Sub

Which filters as expected, however, if I clear the filter and run recorded macro, all results are hidden.

Thoughts/comments please?

Thanks,
Jack

 

[steve the fish]

I always convert dates to long with a VBA filter. Always works then. Had similar problems with the criteria being a date.

 

[JackDanIce]

Thanks Steve, about to head out but will try that. I actually found a 2015 post of yours moaning about dates in VBA to some OP!

 

[JackDanIce]

Thanks @steve the fish, CLNG seems to have fixed it:

Select Case LCase$(rng.Value)
        Case "due next year": dataRng.AutoFilter Field:=10, Criteria1:=">" & CLng(DateSerial(adjYear, 6, 30))
        Case "due within 7 days": dataRng.AutoFilter Field:=10, Criteria1:="<" & CLng(Date + 7)
    End Select