What is the VBA code to Filter out the dates Less than year 2014?

[sanmaya]

Hi All,

Can anybody suggest me the VBA code to Filter the dates where the Year for the date is Less than 2014.

i have set of records and the date value is in column D of excel sheet. the format is the date values are dd-mmm-yy.i want to filter the records whose dates are before year 2014.

it will be appreciated .

 

[mancubus]

i assume topleft cell is A1 and date format in international settings is european date format (DMY)

i'm copying 2 codes that i use regularly for different purposes.

Sub FilterOnDateCopy()

    Dim CritDate As Date
    CritDate = DateSerial(2014, 1, 1)
    
    'Option 1: Copy records before 2014 to new sheet
    With Worksheets("MySheet") 'change sheet name to suit
        .AutoFilterMode = False
        .Cells(1).AutoFilter Field:=4, Criteria1:="<" & CLng(CritDate)
        .AutoFilter.Range.Copy Destination:=Worksheets("NewSheet").Range("A1")
        .AutoFilterMode = False
    End With
    
End Sub

Sub FilterOnDateCopyDelete()

    Dim CritDate As Date
    CritDate = DateSerial(2014, 1, 1)
    
    'Option 2: Copy records before 2014 to new sheet and delete
    With Worksheets("MySheet") 'change sheet name to suit
        .AutoFilterMode = False
        .Cells(1).AutoFilter Field:=4, Criteria1:="<" & CLng(CritDate)
        .AutoFilter.Range.Copy Destination:=Worksheets("NewSheet").Range("A1")
        .UsedRange.Columns(1).Offset(1).SpecialCells(12).EntireRow.Delete
        .AutoFilterMode = False
    End With

End Sub

 

[sanmaya]

i assume topleft cell is A1 and date format in international settings is european date format (DMY)

i'm copying 2 codes that i use regularly for different purposes.

Sub FilterOnDateCopy()

    Dim CritDate As Date
    CritDate = DateSerial(2014, 1, 1)
    
    'Option 1: Copy records before 2014 to new sheet
    With Worksheets("MySheet") 'change sheet name to suit
        .AutoFilterMode = False
        .Cells(1).AutoFilter Field:=4, Criteria1:="<" & CLng(CritDate)
        .AutoFilter.Range.Copy Destination:=Worksheets("NewSheet").Range("A1")
        .AutoFilterMode = False
    End With
    
End Sub

Sub FilterOnDateCopyDelete()

    Dim CritDate As Date
    CritDate = DateSerial(2014, 1, 1)
    
    'Option 2: Copy records before 2014 to new sheet and delete
    With Worksheets("MySheet") 'change sheet name to suit
        .AutoFilterMode = False
        .Cells(1).AutoFilter Field:=4, Criteria1:="<" & CLng(CritDate)
        .AutoFilter.Range.Copy Destination:=Worksheets("NewSheet").Range("A1")
        .UsedRange.Columns(1).Offset(1).SpecialCells(12).EntireRow.Delete
        .AutoFilterMode = False
    End With

End Sub

Thanks a Lot for your Help