自动筛选，列的格式设置为日期 [英] Autofilter with column formatted as date

问题描述

我正在Excel中使用带有VBA的自动筛选器，可用于常规筛选器，但不适用于格式化为日期的列。

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

我可以手动对其进行筛选。如果我运行我的代码，它将过滤任何东西，但是当我检查过滤器然后仅单击确定（未对过滤条件应用更改）时，它将正确过滤。

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.

这是我的代码：

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.

推荐答案

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

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

有时我发现一个人可以使用日期的数值，特别是在处理日期时

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.

这篇关于自动筛选，列的格式设置为日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！