使用变量设置Autofilter字段触发运行时错误1004的求助

通过搜索表头获取列索引用于Autofilter的Field参数时，触发运行时错误1004：Range类的Autofilter方法失败，调试确认变量已存储正确列号，但问题依旧。

原代码 #

Private Sub cmdExtract1_Click()
Dim ws As Worksheet
    Dim lngLastRow As Long
    Dim rngData As Range
    Dim iColNumber As Integer
    
    
     Dim strSearch As String
    Dim aCell As Range
  
    Set ws = Worksheets("Detail Excel")
    ws.Activate


    'Identify the last row and use that info to set up the Range
    With ws
    ws.Range("1:1").Select
        lngLastRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

strSearch = "Deleted App"

    Set aCell = Sheet1.Rows(1).Find(What:=strSearch, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    
     iColNumber = aCell.Column
         
    End With
    

'Offer Date: include dates, remove blanks
Application.DisplayAlerts = False 'switching off the alert button
ws.Range("A1" & ":y" & lngLastRow).AutoFilter Field:=iColNumber, Criteria1:=""
ws.Range("A2" & ":y" & lngLastRow).SpecialCells(xlCellTypeVisible).Delete
Application.DisplayAlerts = True 'switching on the alert button

On Error Resume Next
ws.ShowAllData
End Sub

错误原因分析 #

1. Field参数混淆：工作表列号 vs 筛选区域相对索引
   Autofilter的Field参数是相对于筛选区域的列索引，而非整个工作表的列号。原代码中iColNumber = aCell.Column获取的是工作表的列号（比如Y列是25），但如果筛选范围是A1:Yxxx，此时Field参数最大只能是25；如果目标列在筛选范围之外，直接用工作表列号会导致参数越界报错。

2. 工作表对象不匹配
   查找列时用了Sheet1.Rows(1)，但后续操作的是ws = Worksheets("Detail Excel")，若Sheet1不是目标工作表，会导致aCell指向错误位置，即使列号巧合正确，也可能引发筛选范围与目标列不匹配的问题。

3. 未处理查找失败的情况
   如果表头中不存在"Deleted App"，aCell会是Nothing，执行iColNumber = aCell.Column会直接报错，后续Autofilter自然无法正常运行。

4. 范围拼接写法不规范
   ws.Range("A1" & ":y" & lngLastRow)的拼接方式容易出错，正确写法应为ws.Range("A1:Y" & lngLastRow)。

修正后的代码 #

Private Sub cmdExtract1_Click()
    Dim ws As Worksheet
    Dim lngLastRow As Long
    Dim iColNumber As Integer
    Dim strSearch As String
    Dim aCell As Range
    Dim filterRange As Range
    
    '指定目标工作表，避免激活操作
    Set ws = Worksheets("Detail Excel")
    
    With ws
        '获取有效数据最后一行
        lngLastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        '定义筛选范围：A1到Y列最后一行
        Set filterRange = .Range("A1:Y" & lngLastRow)
        
        strSearch = "Deleted App"
        '在目标工作表的表头行查找列
        Set aCell = .Rows(1).Find(What:=strSearch, LookIn:=xlValues, _
                                  LookAt:=xlWhole, SearchOrder:=xlByRows, _
                                  SearchDirection:=xlNext, MatchCase:=False, _
                                  SearchFormat:=False)
        
        '判断是否找到目标列
        If aCell Is Nothing Then
            MsgBox "未找到""Deleted App""列", vbExclamation
            Exit Sub
        End If
        
        '计算筛选区域内的相对列索引（关键修正）
        iColNumber = aCell.Column - filterRange.Column + 1
        
        '检查目标列是否在筛选范围内
        If iColNumber > filterRange.Columns.Count Then
            MsgBox "目标列不在筛选范围内", vbExclamation
            Exit Sub
        End If
    End With
    
    '筛选空值并删除可见行
    Application.DisplayAlerts = False
    With filterRange
        .AutoFilter Field:=iColNumber, Criteria1:=""
        '判断是否有可见数据行（排除表头）
        On Error Resume Next
        Dim visibleRows As Range
        Set visibleRows = .Offset(1).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        
        If Not visibleRows Is Nothing Then
            visibleRows.Delete
        End If
        '取消筛选
        .AutoFilter
    End With
    Application.DisplayAlerts = True
End Sub

关键修改点 #

• 将Sheet1.Rows(1).Find改为ws.Rows(1).Find，确保查找范围与操作工作表一致。
• 计算iColNumber时，转换为筛选区域的相对索引：aCell.Column - filterRange.Column + 1。
• 增加查找失败和目标列超出筛选范围的判断，避免无意义报错。
• 移除不必要的Activate和Select操作，提升代码稳定性。
• 增加可见行存在性判断，避免SpecialCells报错。

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