Filter rows based on their Year(CriterionDate) in VB

Question

Let's suppose we have an Excel Sheet called "Sheet1" from which I would like to filter/copy/Paste rows to another "Sheet2" within the same workBook. Sheet1 has a column of Date type which name is "DateColumn". How can I use the AutoFilter command to filter only rows having their Year(DateColumn) >= 2000 and then Copy/Paste the filtered rows to the destination sheet "Sheet2". Many thanks in advance.

If your Date column is Column A then this will filter for dates >= to Jan 1, 2000 Columns("A:A").AutoFilter Field:=1, Criteria1:=">=1/01/2000" — Davesexcel
@Davesexcel : First of all thank you for your prompt answer. Actually, I would like to find a way of how to use Month() and Year() functions within an AutoFilter command. It looks to be easy in the case above, but what if I wanted to filter using Year(ColumnA)=2000 and Month(ColumnA)=K (K is my months counter which I do loop from 1 up to 12) ? — sasuke

Unknown Unknown · Accepted Answer · 2015-05-13T23:23:00

I know that you would like to use Month() and Year() functions within the criteria to filter the data, however I would like to propose the following solution to filter the data although the Month() and Year functions are only used to call the procedure.

This procedure requires the range to be filtered, the month and year. Mont and Year are optional as it allows to filter the dates for:

1.- An entire year (all months)

2.- A Year\Month

3.- A Month (all years)

Please note that the dates in the range should have the same format and that format should be used to generate the value for the variable “sCriteria2” (see below details)

I have not included copying the range anywhere it is already done. The procedure can be called this way:

Call Rng_RangeFilterbyYearMonth(RngSrc, Year(dDate), Month(dDate))

Hope you find this useful

Sub Rng_RangeFilterbyYearMonth(ByVal RngSrc As Range, Optional iYear As Integer, Optional iMonth As Byte)
Dim btyFltrType As Byte
Dim sCriteria2 As String

    Rem Validate Year & Month
    If iYear + iMonth = 0 Then Exit Sub

    Rem Filter Range
    With ActiveSheet.Range("$B$6:$C$138")
        On Error Resume Next
        ActiveSheet.ShowAllData
        On Error GoTo 0

        If iYear <> 0 And iMonth = 0 Then
            Rem Filter by Year (all months)
            Rem .AutoFilter Field:=1, Operator:=xlFilterValues, Criteria2:=Array(0, "9/29/2009", 0, "1/15/2004")
            btyFltrType = 0
            sCriteria2 = "1/1/" & iYear
            .AutoFilter Field:=1, Operator:=xlFilterValues, Criteria2:=Array(btyFltrType, sCriteria2)

        ElseIf iYear <> 0 And iMonth <> 0 Then
            Rem Filter by Year/Month
            Rem Variable sCriteria2 must be generated using the date format of the data in the range
            Rem The dates in the range should have the same format
            Rem For [mm/dd/yyy] use sCriteria2 = iMonth & "/1/" & iYear
            Rem For [dd/mm/yyy] use sCriteria2 = "1/" & iMonth & "/" & iYear
            btyFltrType = 1
            sCriteria2 = "1/" & iMonth & "/" & iYear
            .AutoFilter Field:=1, Operator:=xlFilterValues, Criteria2:=Array(btyFltrType = 1, sCriteria2)

        Else
            Rem Filter by Month (All Years)
            btyFltrType = 20 + iMonth
            .AutoFilter Field:=1, Criteria1:=btyFltrType, Operator:=xlFilterDynamic

    End If: End With

End Sub

Filter rows based on their Year(CriterionDate) in VB

2 Answers