列格式为日期的自动过滤器 [英] Autofilter with column formatted as date

查看:44
本文介绍了列格式为日期的自动过滤器的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

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 自动筛选器,日期可能会很棘手.有些人发现循环遍历要过滤的数组更容易.

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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆