vba 列格式为日期的自动过滤器

Autofilter with column formatted as date

提问by Spurious

I am using an AutoFilter with VBA in Excel that works for regular filters, but not a column formatted as date.

我在 Excel 中使用带有 VBA 的 AutoFilter，它适用于常规过滤器，但不适用于格式化为日期的列。

I can filter it manually. If I run my code, it filters nothing but when I check the filter and then only click ok (no change being applied to the filter criteria), it filters correctly.

我可以手动过滤它。如果我运行我的代码，它不会过滤任何东西，但是当我检查过滤器然后只单击“确定”（没有对过滤器标准应用任何更改）时，它会正确过滤。

Here is my code:

这是我的代码：

ws.ListObjects(SheetName).Range.AutoFilter Field:=3, Criteria1 _
        :=">" & CDate([datecell]), Operator:=xlAnd, Criteria2:= _
        "<=" & CDate(WorksheetFunction.EoMonth([datecell], 3))

It seems to be a common problem, but I have not found a solution.

这似乎是一个常见问题，但我还没有找到解决方案。

When I run a recorded macro, it does not work either.

当我运行录制的宏时，它也不起作用。

回答by Ron Rosenfeld

Dates can be tricky with Excel VBA AutoFilter. Some find it easier to just loop through the array to be filtered.

Excel VBA AutoFilter 的日期可能会很棘手。有些人发现循环遍历要过滤的数组更容易。

Sometimes I have found that one can use the numeric value of the date, especially when dealing with "dates between"

有时我发现可以使用日期的数值，尤其是在处理“日期之间”时

Criteria1:= ">" & CDbl([datecell])
Criteria2:= "<=" & CDbl(WorksheetFunction.EoMonth([datecell], 3))

Note that the above need to be "real dates" and not strings that look like dates. Even a single "string date" will mess things up.

请注意，以上需要是“真实日期”，而不是看起来像日期的字符串。即使是一个“字符串日期”也会把事情搞砸。

回答by Makah

Autofilter()works with 'universal' format yyyy-mm-dd, i.e.:

Autofilter()适用于“通用”格式yyyy-mm-dd，即：

Criteria1:= ">" & Format([MY_DATE],"yyyy-mm-dd")
Criteria2:= "<=" & Format([MY_DATE],"yyyy-mm-dd")

It's better because Excel can't 'understand' it wrong . If you use mm/dd/yyyyor dd/mm/yyyyExcel can fit 02/jan as 01/feb.

更好，因为 Excel 不能“理解”它错误。如果您使用mm/dd/yyyy或dd/mm/yyyyExcel 可以将 02/jan 设为 01/feb。

see: http://www.oaltd.co.uk/ExcelProgRef/Ch22/ProgRefCh22.htm

见：http: //www.oaltd.co.uk/ExcelProgRef/Ch22/ProgRefCh22.htm

The Rules for Working with Excel (International Issue)

2. When you have to convert numbers and dates to strings for passing to Excel (such as in criteria for AutoFilter or .Formula strings), always explicitly convert the data to a US-formatted string, using Trim(Str(MyNumber)), or the sNumToUS() function shown earlier, for all number and date types. Excel will then use it correctly and convert it to the local number/date formats.

使用 Excel 的规则（国际问题）

2. 当您必须将数字和日期转换为字符串以传递给 Excel 时（例如在 AutoFilter 或 .Formula 字符串的条件中），请始终使用 Trim(Str(MyNumber)) 或sNumToUS() 函数，适用于所有数字和日期类型。然后 Excel 将正确使用它并将其转换为本地数字/日期格式。

---

---

Edit:

编辑：

We can create an universal Function using Application.Internationallike:

我们可以使用Application.International类似的方法创建一个通用函数：

Sub MySub()
    Select Case application.International(xlDateOrder)
        Case Is = 0
            dtFormat = "mm/dd/yyyy"
        Case Is = 1
            dtFormat = "dd/mm/yyyy"
        Case Is = 2
            dtFormat = "yyyy/mm/dd"
        Case Else
            dtFormat = "Error"
    End Select

    Debug.Print (dtFormat)

    ...
    Criteria1:= ">" & Format([MY_DATE],dtFormat)
    Criteria2:= "<=" & Format([MY_DATE],dtFormat)
    ...

End Sub

回答by Karlos Henrique

you need to convert the format to the american format, like: ">" & Format([datecell], "mm/dd/yyyy") VBA does not understand another format.

您需要将格式转换为美国格式，例如：">" & Format([datecell], "mm/dd/yyyy") VBA 无法识别另一种格式。

回答by Mago Fabian

This syntax works for me:

这种语法对我有用：

.AutoFilter Field:=2, Operator:=xlFilterValues, Criteria2:=Array(2, Format(Now, "yyyy-mm-dd"))

Hint obtained through a macro registration

通过宏注册获得的提示

回答by Graham P

Match your "dd-mm-yyy" to the format of the column, so if you have "16-Aug-16" as your source data formatting then make the filter as "dd-mmm-yy"

将您的“dd-mm-yyy”与列的格式匹配，因此如果您将“16-Aug-16”作为源数据格式，则将过滤器设为“dd-mmm-yy”

回答by Andrés Roldán

One solution is to set up correctly the format of the target column. If its formatted as "custom", even if it looks like a date, it won't work.

一种解决方案是正确设置目标列的格式。如果它的格式为“自定义”，即使它看起来像一个日期，它也不会起作用。

You are asking VBA to filter based on a date, therefore the format of the columns has to be a date too.

您要求 VBA 根据日期进行过滤，因此列的格式也必须是日期。

回答by pmyk

Karlos Henrique, Thanks for suggesting Format([datecell], "mm/dd/yyyy"). It works in my file. My previous code is :

Karlos Henrique，感谢您建议 Format([datecell], "mm/dd/yyyy")。它适用于我的文件。我以前的代码是：

Range("A1:D" & Trim(Str(LstRec))).AutoFilter Field:=2, _
  Criteria1:=">=" & StrtDt, _
  Operator:=xlAnd, _
  Criteria2:="<=" & EndDt

My modified code is :

我修改后的代码是：

Range("A1:D" & Trim(Str(LstRec))).AutoFilter Field:=2, _
  Criteria1:=">=" & Format(StrtDt, "mm/dd/yyyy"), _
  Operator:=xlAnd, _
  Criteria2:="<=" & Format(EndDt, "mm/dd/yyyy")

Thanks.

谢谢。

回答by Sudhanshu Soni

SO this worked for me pretty clean

所以这对我来说很干净

ActiveSheet.Range("$A:$K727").AutoFilter Field:=1, Criteria1:= _
        ">=" & Range("G1"), Operator:=xlAnd, Criteria2:="<=" & Range("H1")

You can try this as well

你也可以试试这个

Expected output will start date in my G1 cell and end date will be H1 cell.

预期输出将在我的 G1 单元格中开始日期，结束日期将是 H1 单元格。

回答by Piotr Kotarba

I had similar problem, while filtering column with date. When I changed system date format to 01/01/2020, it converted my date to 1/1/2020 in Criteria field. My solution was to convert arguments to str during setting to function.

我在过滤带有日期的列时遇到了类似的问题。当我将系统日期格式更改为 01/01/2020 时，它在 Criteria 字段中将我的日期转换为 1/1/2020。我的解决方案是在设置为函数期间将参数转换为 str 。

回答by Joe Messingschlager

here's the occam's razor solution... try putting this in Autoopen for the spreadsheet or if you need to, modify it for the sheet that you wish to affect. it will cause the drop down filters for the date headers to appear as individual dates and not as a date hierarchy.

这是 occam 的 razor 解决方案......尝试将其放入电子表格的 Autoopen 中，或者如果需要，为您希望影响的工作表修改它。它会导致日期标题的下拉过滤器显示为单个日期而不是日期层次结构。

ActiveWindow.AutoFilterDateGrouping = False

ActiveWindow.AutoFilterDateGrouping = False