尝试过滤时，运行时错误“1004”应用程序定义或对象定义错误 [英] Run-time error '1004' application-defined or object-defined error when trying to filter

问题描述

我在尝试过滤数据集时遇到问题。每次我尝试使用range.autofilter选项时，都会得到运行时错误1004：应用程序定义或对象定义的错误。



请查找我的代码的一部分

Hi,

I'm having problems trying to filter my data set. Each time I try to use the range.autofilter option, I get the Run-time error '1004': Application-defined or object-defined error.

Please find a part of my code below

'Filter sheet RSPS5 & delete 'CLOSED' rows
With wbDest.Sheets("RSPS5")
    .Activate
    LastRowDest = .Range("B" & Rows.Count).End(xlUp).Row
End With
wbDest.Sheets("RSPS5").Range("B4:DX" & LastRowDest).AutoFilter Field:=20, Criteria1:="CLOSED", Header:=xlYes
With wbDest.Sheets("RSPS5")
    .Range("U7:U" & LastRowDest).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    LastRowDest = .Range("B" & Rows.Count).End(xlUp).Row
End With
wbDest.Sheets("RSPS5").Range("B4:DX" & LastRowDest).AutoFilter Field:=20

wbDest已在宏之前定义，并且在此部分之前的代码正常工作。

我想要的列过滤器是'U'（如果从'B'列开始计数，则应该是第20列）。



我尝试了什么：



有一些冗余代码。我一直在尝试通过在过滤器行前面明确添加工作簿和工作表来使其工作的几种方法。

奇怪的是，在调试时，我可以看到工作簿，工作表和范围是定义的。

我可以手动过滤包含已关闭文本的行，这样也不应该是我的经验不足的问题。



我可能会忽略一些东西，所以如果有人能指出我正确的方向，我会非常激动。

wbDest has been defined earlier in the macro and is working properly for the code before this part.
The column I want to filter on is 'U' (and should be the 20th column if you start counting from column 'B').

What I have tried:

There is some redundant code. I've been trying several ways to make it work by explicitly adding workbooks and worksheets in front of the filter lines.
The odd part is that, while debugging, I can see that the workbook, worksheet and range is defined.
I can manually filter on lines containing "CLOSED" text so that shouldn't be an issue either, in my inexperienced opinion.

I'm probably overlooking something, so if anybody could point me in the right direction, I'd be super thrilled.

推荐答案

刚刚确认 Range.AutoFilter method（Excel）中的MSDN文档Microsoft Docs [ ^ ]命名参数 Header 不正确所以

Just confirmed on the MSDN documentation at Range.AutoFilter method (Excel) | Microsoft Docs[^] that the named parameter Header is incorrect so

wbDest.Sheets("RSPS5").Range("B4:DX" & LastRowDest).AutoFilter Field:=20, Criteria1:="CLOSED", Header:=xlYes

应该是

wbDest.Sheets("RSPS5").Range("B4:DX" & LastRowDest).AutoFilter Field:=20, Criteria1:="CLOSED"

我有能够得到类似的代码来过滤我自己的VBA。

I have been able to get similar code to filter in my own VBA.

这篇关于尝试过滤时，运行时错误“1004”应用程序定义或对象定义错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！