从Excel筛选列提取唯一值粘贴时触发运行时错误1004如何解决

报错原因排查 #

• AdvancedFilter 方法要求操作的数据源区域必须包含表头行，你当前代码中定义的DataRng是通过.Offset(1, 10)从原数据集的第二行开始取值，没有包含表头，不符合方法要求。
• SpecialCells(xlCellTypeVisible)返回的是筛选后被拆分的不连续多区域范围，AdvancedFilter无法直接对不连续的区域执行操作，这是触发本次报错的核心原因。
• 你在设置DataRng后立即调用了.AutoFilter关闭了原表筛选，虽然不直接触发报错，但如果后续依赖筛选状态的操作会受影响。

修正方案 #

你可以先把筛选后的可见值复制到临时空白区域，生成带表头的连续区域后再执行高级筛选，示例代码如下：

' 新增临时表存储筛选后的连续数据
Dim TempSht As Worksheet
Set TempSht = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
' 补全高级筛选需要的表头，对应原数据偏移10列的表头位置
TempSht.Range("A1").Value = MainSht.Cells(1, 11).Value

' 明确绑定单元格所属工作表，避免激活页切换导致范围定义错误
Set DataSet = MainSht.Range(MainSht.Cells(1, 1), MainSht.Cells(Lrows, Lcols))

With DataSet
    .AutoFilter field:=3, Criteria1:=Array("Corporate Treasury - US", "F&A"), Operator:=xlFilterValues
    ' 筛选后可见值直接粘贴到临时表生成连续区域
    .Offset(1, 10).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible).Copy TempSht.Range("A2")
    .AutoFilter ' 关闭原表筛选
End With

' 对临时表的连续带表头区域执行高级筛选，直接输出到目标表
TempSht.UsedRange.AdvancedFilter Action:=xlFilterCopy, _
    copytorange:=Wb.Sheets("Corporate Treasury - US").Range("A2"), Unique:=True

' 操作完成后删除临时表
Application.DisplayAlerts = False
TempSht.Delete
Application.DisplayAlerts = True

其他注意事项 #

• 定义Range对象时建议明确绑定父工作表，你原代码中的Cells(1,1)没有指定所属工作表，如果执行代码时当前激活的不是MainSht，会导致DataSet范围定义错误，也可能间接触发该报错。
• 目标表的粘贴起始位置如果已经有数据，高级筛选会直接覆盖原有内容，操作前可以先清空目标表对应列避免数据混淆。

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