自动过滤器一个范围，不包括两个日期 [英] Autofilter a Range excluding two dates

问题描述

我有以下代码，在L1中，我想选择除昨天和今天之外的所有日期，并且在M1中选择所有日期。我可以在M1中做同样的操作，但无法在L1中执行操作。

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:="="

从用户的意见：

我有从列A到U的数据。我有很多标准来选择每一列。在列L中只有日期。我想要实现的是取消选择昨天和今天的日期，并选择所有其他日期。而已。我的任务将在那里完成

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

推荐答案

^{在列L中只有日期。我想要实现的是取消选择昨天和今天的日期，并选择所有其他日期。而已。}

过滤所有不是今天或昨天的日期。

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

请注意，这是在电脑上测试系统具有MDY区域设置。如果它不适用于您，您的系统是DMY，请更改日期格式。

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.

^{之前自动过滤应用}

< img src =https://i.stack.imgur.com/AuokW.pngalt =not_today_yesterday> ^{应用Autofilter之后（End Sub之前）}

这篇关于自动过滤器一个范围，不包括两个日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！