View original (French)

VBA Excel: Filter by Year Only

Solved
nenuf -  
Morgothal Posted messages 1350 Status Member -
Hello everyone,

A quick question about VBA, filters, and dates.
I have data in a column in the format "dd/mm/yyyy", and I want to filter in VBA by selecting only the year...

I managed to retrieve the list of possible years in a cell named "Year", I just want to modify the following line:

ActiveSheet.Range("$B$12:$AE$2500").AutoFilter Field:=4, Criteria1:=Year

Because here, of course, it finds nothing since my column doesn't only contain the year... One clarification: I can't modify my table to insert a column or anything else.

Thanks in advance!

1 answer

  1.
    Morgothal Posted messages 1350 Status Member 184
     
    Hello,
    Maybe by replacing "Annee" with "Year(Annee)"?

    --
    -------------------
    Best regards,
    Clément
    0
    1. nenuf
       
      Unfortunately not, I did try, but at the filter stage the entire column disappears because no data matches; we really need to add a column...

      Unless I set a criterion like >= 00/00/Year and <00/00/Year+1! I will explore this avenue.
      0
    2. nenuf
       
      It's good, I found a technique that works:

      ActiveSheet.Range("$B$12:$AE$2503").AutoFilter Field:=1, Criteria1:=">=1/1/" & Year, Operator:=xlAnd, Criteria2:="<=12/31/" & Year
      0
    3. Morgothal Posted messages 1350 Status Member 184
       
      Ah perfect then! :-)
      0