Help - Filter Date Bug

  • Hi all,

    I have a file which is behaving quite strangely when filtering. I have attached a small copy. Basically I normally save dates as long, and then filter using the date serial. However on the attached something weird is going on - my vba doesn't seem to pick it up as a date or a long number. Also if you try manually filtering and typing in "01/06/2016" it shows nothing, yet if you go through the tick box and select June it works. Also if I set Jun as a string it seems to pick it up when I don't think it should be able to!

    I have never come across this before, can anyone advise?

    Dim Ldte as long
    DataLstRw = ThisWorkbook.Sheets("Data").UsedRange.Rows.Count
    DataLstCl = ThisWorkbook.Sheets("Data").UsedRange.Columns.Count
    LDte = DateSerial(Year(Date), Month(Date) - 1, 1)ThisWorkbook.Sheets("Data").Range(Cells(1, 1), Cells(DataLstRw, DataLstCl)).AutoFilter Field:=3, Criteria1:=LDte, Operator:=xlAnd
  • Re: Help - Filter Date Bug

    As an update, this seems to filter

    LDte = DateSerial(Year(Date), Month(Date) - 1, 1)
    ThisWorkbook.Sheets("Data").Range(Cells(1, 1), Cells(DataLstRw, DataLstCl)).AutoFilter Field:=3, Criteria1:=Format(LDte, "MMM YY"), Operator:=xlAnd

    So Im now stumped at why I cant filter on it as a date serial like I normally do. I thought if it was held in this format it would always work? If I change the "format" of the column to general, the code above wont work (which I wouldn't think it would) but I could really do with understanding whats going on!

  • Re: Help - Filter Date Bug

    Right I answered my own question. It looks it is a bug. fixed it with this. Thats mad it doesn't work like it should

    ThisWorkbook.Sheets("Data").Range(Cells(1, 1), ThisWorkbook.Sheets("Data").Cells(DataLstRw, DataLstCl)).AutoFilter field:=DataMonth, Criteria1:=">=" & LDte, Operator:=xlAnd, Criteria2:="<" & LDte + 1

    Have a good weekend all.


  • Re: Help - Filter Date Bug

    DateSerial should work with AutoFilter - see this, it's not a bug!

    I can't say why it hasn't worked for you because you did not include your code in the attachment

  • Re: Help - Filter Date Bug

    Hi Roy,

    That link actually shows my problem.. I wasn't aware of this limitation previously, but surely it must be a bug?

    "FILTER BY EXACT DATE It does apply and filter the list of dates in Column "A" but no data shows even though the date does exist. The only work-around I know is to use 2 criteria and make the second date criteria 1 day after the needed date. "

    This was my exact problem, and I came to same conclusion whilst testing (though had I seen this link earlier I would have saved quite abit of time!).

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!