Click here to Skip to main content
14,661,076 members
Rate this:
Please Sign up or sign in to vote.
See more:

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")
    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 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.
Updated 24-Jul-20 5:44am
RedDk 30-Apr-19 14:09pm
Excel Error 1004 is the outermost generic error ... it comes back as a message alert to just about anything and everything Excel FAIL. So you'll have to debug to get at particulars.
CHill60 1-May-19 11:25am
Assuming it is the second autofilter that is failing - try removing the autofilter already in place before putting the new one in
b.wag 2-May-19 3:10am
I can't even get past the first autofilter.
In other files, where a macro is using autofilter as well, it worked fine before. From this point forward, I can't get any autofilter code to work.
CHill60 2-May-19 3:36am
Ah - my misunderstanding. If you put a breakpoint on the first autofilter and use
? wbDest.Sheets("RSPS5").Range("B4:DX" & LastRowDest).Address
do you get an error or the address output? E.g. $B$4:$DX$200
CHill60 2-May-19 3:41am
Hang on … I don't recall Header being an option … try
wbDest.Sheets("RSPS5").Range("B4:DX" & LastRowDest).AutoFilter Field:=20, Criteria1:="CLOSED"
b.wag 2-May-19 4:02am
Thank you, that did the trick indeed. I had been staring at this issue for hours and I looked up the MSDN documentation as well but somehow my brain must have skipped the Header part.
CHill60 2-May-19 4:24am
LOL! I missed it I think because it was on the next line. Glad it's sorted now.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

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
should be
wbDest.Sheets("RSPS5").Range("B4:DX" & LastRowDest).AutoFilter Field:=20, Criteria1:="CLOSED"
I have been able to get similar code to filter in my own VBA.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100