Autofilter a Range excluding two dates

2019-07-23 22:56发布

I have the below code, In L1 I want to select all dates except for yesterday and today and in M1 unselect all. I able to do the same in M1 but unable to perform the action in L1.

Range("L1").Select
ActiveSheet.Range("$A$1:$U$3804").AutoFilter Field:=12, Operator:= _
    xlFilterValues, Criteria2:=Array(1, "2/11/2016", 1, "3/31/2016", 2, "4/5/2016", 2, _
    "4/6/2016", 2, "4/7/2016", 2, "4/8/2016", 2, "4/11/2016", 2, "4/12/2016", 2, "4/13/2016", _
    2, "4/14/2016", 2, "4/15/2016", 2, "4/18/2016", 2, "4/19/2016", 2, "4/20/2016", 2, _
    "4/21/2016", 2, "4/22/2016", 2, "4/25/2016", 0, "10/28/2015")
Range("M1").Select
ActiveSheet.Range("$A$1:$U$3804").AutoFilter Field:=13, Criteria1:="="

From user's Comments:

I have data from Column A to U. I have many criterias to select in every column. In Column L there are only dates. What I want to achieve is Unselect dates of yesterday and today and select all other dates present. thats it. My task will be completed there

1条回答
贼婆χ
2楼-- · 2019-07-23 23:17

In Column L there are only dates. What I want to achieve is Unselect dates of yesterday and today and select all other dates present. thats it.

Filter for all dates that are not today or yesterday.

Option Explicit

Sub notTodayOrYesterday()
    With Worksheets("Sheet5")
        If .AutoFilterMode Then .AutoFilterMode = False
        With .Cells(1, 1).CurrentRegion
            .AutoFilter Field:=12, Criteria1:=Format(Date, "\<\>mm/dd/yyyy"), _
                        Operator:=xlAnd, Criteria2:=Format(Date - 1, "\<\>mm/dd/yyyy")
        End With
        If .AutoFilterMode Then .AutoFilterMode = False
    End With
End Sub

Note that this was tested on a computer system with MDY regional settings. If it does not work for you and your system is DMY, change the date formats.

not_today_yesterday
            Before Autofilter applied

not_today_yesterday
            After Autofilter applied (before End Sub)

查看更多
登录 后发表回答