vba 根据日期筛选

1.根据日期筛选

如下excel数据，A列为日期格式，D列为常规格式，单元格最前面有单引号 ' 

EXCEL数字前加单引号可以把数字当成文本来处理，输入的是什么，展示的就是什么

 

 

 

 

对B列进行筛选的vba语句为

ActiveSheet.Range("$A$1:$D$13").AutoFilter Field:=2, Criteria1:="C组"

对D列进行筛选的vba语句为

ActiveSheet.Range("$A$1:$D$13").AutoFilter Field:=4, Criteria1:="2023-02-02"

对A列日期格式进行筛选时，若vba语句为

ActiveSheet.Range("$A$1:$D$13").AutoFilter Field:=1, Criteria1:="2023-02-02"

语句不会报错，但什么都筛选不出来，录制出来的语句为

Selection.AutoFilter
ActiveSheet.Range("$A$1:$D$13").AutoFilter Field:=1, Operator:= _
xlFilterValues, Criteria2:=Array(2, "2/2/2023")

但执行不了，会报错

 

 

对A列日期格式数据进行筛选

解决方案：

将A列日期格式数据先转换为文本格式，然后再进行筛选

代码为：

Sub select_dat()
    Dim d_date As Date
    Dim l_date As Long
    d_date = DateSerial(2023, 2, 2)
    l_date = d_date
    
    Columns("A:A").Select
    Selection.NumberFormatLocal = "@"   '单元格格式设置为文本格式
    ActiveSheet.Range("$A$1:$D$13").AutoFilter Field:=1, Criteria1:=l_date, Operator:=xlAnd
    
End Sub

 

最后效果为

 

 

 

 

 

 

 

 

问题描述

在工作的时候经常碰到这样的问题，因为系统日期格式不是美式标准，所以用VBA做日期筛选的时候，VBA会试着把月和日的数字交换，例如把1/2/2017（2017年1月2日）理解成2/1/2017（2017年2月1日），不过13/1/2017不会受到影响，因为13不是有效的月份。

注意事项

澳大利亚的标准日期格式为：日/月/年，这是我们想要使用的格式

美国的标准日期格式为：月/日/年，VBA强行使用的格式

举个栗子

下面举个小栗子。我们想要用VBA筛选下图中2017年1月1日到2017年1月10日的数据。

24行数据

一开始的代码为（知乎好像不支持VBA高亮？）

Option Explicit
Sub demo()
    Dim table As ListObject
    Dim startDate As Date
    Dim endDate As Date
    
    startDate = DateSerial(2017, 1, 1)
    endDate = DateSerial(2017, 1, 10)
    
    Set table = ActiveSheet.ListObjects("demoTable")
    table.Range.AutoFilter field:=1, Criteria1:=">=" & startDate, _
    Criteria2:="<=" & endDate
    
End Sub

得到的结果如下图

虽然不清楚，不过还是24行数据

仔细看一下对应的筛选条件，可以发现VBA把1和10交换了（日期顺序为日/月/年）。所以实际应用的筛选条件为筛选所有2017年1月1日到2017年10月1日的数据。

解决方案

解决方法其实非常简单，因为日期在Excel中只是一串整数，所以我们可以直接用Long type来表示日期，这样VBA就不会误解了。具体代码为

Option Explicit

Sub demo()
    Dim table As ListObject
    Dim startDate As Date
    Dim endDate As Date
    
    Dim lStartDate As Long
    Dim lEndDate As Long
    
    startDate = DateSerial(2017, 1, 1)
    endDate = DateSerial(2017, 1, 10)
    lStartDate = startDate
    lEndDate = endDate
    
    Set table = ActiveSheet.ListObjects("demoTable")
    table.Range.AutoFilter field:=1, Criteria1:=">=" & lStartDate, _
    Criteria2:="<=" & lEndDate
    
End Sub

结果如下图

选了我们想要的数据

这时再看一下对应的筛选条件，完美了。

完美

有些人可能觉得还可以用Format()函数来完成这个操作，但是实际上是不行的，有兴趣的小伙伴可以自己试试看。

最后再说一句，Excel本身并不排斥其他格式的日期，但是VBA在运行的时候只认美国格式。有一个简单的方法可以解决或者帮助debug大部分关于VBA的日期问题，那就是把系统的地区换成美国，日期显示格式也换成美国格式，这样之后就不用使用这里提到的解决方法了，即使Excel中的日期并不是按照月/日/年（美国）格式也没有关系。