VBA Autofilter xlFilterAllDatesInPeriod筛选突发1004错误求助

之前运行数月正常的VBA宏突然失效，执行Autofilter时弹出错误：Run-time Error '1004'：AutoFilter method of Range class failed。测试发现仅筛选二月（xlFilterAllDatesInPeriodFebruary）时失败，其他月份均可正常运行。已尝试简化代码、替换为ActiveSheet测试，问题依旧。

相关代码如下：

Sub Autofilter()

Dim ws1 As Worksheet
Dim strUserName As String
strUserName = Environ("Username")

Dim xlmonth As Long
Dim sd As Variant
sd = Workbooks("Macro Launcher.xlsm").Worksheets("Main Sheet").Range("B2") 'contains current month

If sd = "January" Then xlmonth = xlFilterAllDatesInPeriodJanuary _
Else If sd = "February" Then xlmonth = xlFilterAllDatesInPeriodFebruary _
Else If sd = "March" Then xlmonth = xlFilterAllDatesInPeriodMarch _
Else If sd = "April" Then xlmonth = xlFilterAllDatesInPeriodApril _
Else If sd = "May" Then xlmonth = xlFilterAllDatesInPeriodMay _
Else If sd = "June" Then xlmonth = xlFilterAllDatesInPeriodJune _
Else If sd = "July" Then xlmonth = xlFilterAllDatesInPeriodJuly _
Else If sd = "August" Then xlmonth = xlFilterAllDatesInPeriodAugust _
Else If sd = "September" Then xlmonth = xlFilterAllDatesInPeriodSeptember _
Else If sd = "October" Then xlmonth = xlFilterAllDatesInPeriodOctober _
Else If sd = "November" Then xlmonth = xlFilterAllDatesInPeriodNovember _
Else If sd = "December" Then xlmonth = xlFilterAllDatesInPeriodDecember

Workbooks.Open ("C:\Users\" & strUserName & "\Downloads\DNR BIP 1_ Detail Employee Listing - Active Employee Information.xlsx")
Range("B:C,G:H,J:O,R:W,AA:AL,AQ:AW,AY:AY,BA:BG").Delete Shift:=xlToLeft
Set ws1 = Workbooks("DNR BIP 1_ Detail Employee Listing - Active Employee Information.xlsx").Sheets("Sheet1")
ws1.Range("$A:$P").Autofilter field:=10, Criteria1:="HR" '- I tried removing this one as well, still got the error
'ws1.Range("$A:$P").AutoFilter Field:=11, Criteria1:=xlFilterAllDatesInPeriodFebruary, Operator:=xlFilterDynamic -- trited this as well, did not work

ws1.Range("$A:$P").Autofilter field:=11, Criteria1:= _
       xlmonth, Operator:=xlFilterDynamic '- this is the one where the macro fails

End Sub

排查方向及解决方法 #

• 检查目标列数据有效性：重点查看第11列的日期数据，确认是否存在非日期格式内容、空值或非法日期（如2月30日）。动态日期筛选对数据格式要求严格，异常值会直接触发报错。
• 修正列引用偏移问题：删除列操作后，手动核对第11列是否为预期的日期列。建议改用表头名称定位列（比如通过Cells.Find找到表头所在列），避免列索引因删除操作发生偏移。
• 替换枚举值为数字：xlFilterAllDatesInPeriodFebruary对应的枚举数值是3，直接将代码中xlFilterAllDatesInPeriodFebruary替换为3测试，排除系统区域设置变更导致的枚举值不匹配问题。
• 清除原有筛选状态：在执行新筛选前，先清除工作表的自动筛选状态，避免冲突：

  If ws1.AutoFilterMode Then ws1.AutoFilterMode = False
  

• 明确工作表引用：删除列的代码未指定工作表，可能在错误的工作表执行，导致后续列索引混乱。修改为指定工作簿和工作表：

  Dim targetWB As Workbook
  Set targetWB = Workbooks.Open("C:\Users\" & strUserName & "\Downloads\DNR BIP 1_ Detail Employee Listing - Active Employee Information.xlsx")
  targetWB.Sheets("Sheet1").Range("B:C,G:H,J:O,R:W,AA:AL,AQ:AW,AY:AY,BA:BG").Delete Shift:=xlToLeft
  Set ws1 = targetWB.Sheets("Sheet1")
  

内容的提问来源于stack exchange，提问作者Walentyne