日期条目-高级过滤器

Question

你能帮我吗?

我有一个电子表格，我在VBA中使用高级过滤器。cell "M5"是日期和" 5"是约会对象。这个想法是用户类型"日期"来自"日期"和日期到;并获得过滤的数据。

我正在使用下面的代码，但它不工作。数据根本没有被过滤或部分过滤。有谁能帮帮我吗?

Sub TableFilt()
Dim ToDate, FrDate        As Date
'Improve the performance of the macro
With Application
.ScreenUpdating = FALSE
.DisplayAlerts = FALSE
End With
With Sheet6
LastRow = .Range("E99999").End(xlUp).Row

'If .Range("M5").Value = "From Date" Then FrDate = "1/1/2015" Else: FrDate = .Range("M5").Value                            
'If .Range("N5").Value = "To Date" Then ToDate = "31/12/2030" Else: ToDate = Range("N5").Value                              

.Range("E6:AU" & LastRow).Select

Selection.AutoFilter
With .Range("E6:AU" & LastRow)
.AutoFilter Field:=9, Criteria1:=">=" & FrDate, Operator:=xlAnd, Criteria2:="<=" & ToDate        
End With

End Sub

Answer 1

做了一些改变，对我有用:

Sub TableFilt()
Dim ToDate As Date, FrDate As Date
With Sheet6
LastRow = .Range("E99999").End(xlUp).Row

If .Range("M5").Value = "From Date" Then FrDate = "1/1/2015" Else: FrDate = .Range("M5").Value
If .Range("N5").Value = "To Date" Then ToDate = "31/12/2030" Else: ToDate = .Range("N5").Value
With .Range("E6:AU" & LastRow)
.AutoFilter Field:=9, Criteria1:=">=" & FrDate, Operator:=xlAnd, Criteria2:="<=" & ToDate
End With
End With
End Sub

1. AFAIKDim ToDate, FrDate As Date只会使FrDate暗淡为date，而使ToDate暗淡为variant。应该还能工作，但只是作为一个提示。

2. 我不认为禁用ScreenUpdating或DisplayAlerts时使用自动过滤器的点，所以它被删除。无论如何都不应该有多于一个的改变

3. 这两个Ifs是需要的，所以我们不能把它们作为注释。还有一个范围中缺少的点。

4. 我没有看到选择任何东西的意义，所以它被删除了。无论如何，该区域在下一行中定义。

5. 缺少End With来完成Sub.

Answer 2

日期值不是文本，但需要格式化为标准的字符串表达式，所以试试这个:

Sub TableFilt()
Dim ToDate  As Date
Dim FrDate  As Date
' Improve the performance of the macro
With Application
.ScreenUpdating = FALSE
.DisplayAlerts = FALSE
End With
With Sheet6
LastRow = .Range("E99999").End(xlUp).Row

If .Range("M5").Value = "From Date" Then FrDate = #1/1/2015# Else: FrDate = .Range("M5").Value                            
If .Range("N5").Value = "To Date" Then ToDate = #12/31/2030# Else: ToDate = .Range("N5").Value                              

.Range("E6:AU" & LastRow).Select

Selection.AutoFilter
With .Range("E6:AU" & LastRow)
.AutoFilter Field:=9, Criteria1:=">= #" & Format(FrDate, "yyyy/mm/dd") & "#", Operator:=xlAnd, Criteria2:="<= #" & Format(ToDate, "yyyy/mm/dd") & "#"
End With
End With

End Sub