vba excel vba自动过滤器不过滤日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10687860/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
excel vba autofilter not filtering on dates
提问by Andy5
I have column of dates, which is an output from a database. There is a macro that filters on this date which is meant to show all dates older than 14 days from current date excluding nulls. The filter when applied filters the entire sheet and shows nothing. When I take off the - 14 I do get dates, I have tried even using alt a, e, f to ensure that the column is the correct format, but no luck.
我有一列日期，它是数据库的输出。有一个宏可以在此日期上进行过滤，旨在显示从当前日期起 14 天之前的所有日期，不包括空值。应用过滤器时会过滤整个工作表并且不显示任何内容。当我去掉 - 14 时，我确实得到了日期，我什至尝试使用 alt a、e、f 来确保该列的格式正确，但没有运气。
Here is my code snippet:
这是我的代码片段：
ActiveSheet.UsedRange.AutoFilter Field:=34, Criteria:="<>NULL", _
Operator:=xlAnd, Criteria2:="<" & Now()-14
I have tried changing the criteria2to "<" & Date - 14 & "# 00:00:00 AM#"
我曾试图改变criteria2，以"<" & Date - 14 & "# 00:00:00 AM#"
Please help
请帮忙
回答by Siddharth Rout
Try this
尝试这个
Change Criteria:="<>NULL"to Criteria1:="<>NULL"
更改Criteria:="<>NULL"为Criteria1:="<>NULL"
FOLLOWUP
跟进
The code was not working as the headings were in not in Row 1. Once the exact range was specified, it started working.
代码不起作用，因为标题不在第 1 行。一旦指定了确切的范围，它就开始工作了。
Option Explicit
Sub Sample()
Dim lRow As Long
lRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
ActiveSheet.AutoFilterMode = False
ActiveSheet.Range("$A:$BV$" & lRow).AutoFilter Field:=34, Criteria1:="<>NULL" _
, Operator:=xlAnd, Criteria2:="<" & Format(Date - 14, "0")
End Sub
