Run-time error '1004' application-defined or object-defined error when trying to filter

Question

0.00/5 (No votes)

See more:

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

VB

'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 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.

Posted 26-Apr-19 3:25am

b.wag

Updated 24-Jul-20 5:44am

Add a Solution

Comments

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Accepted Answer · 2019-05-01T21:44:00

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

VB

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

should be

VB

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.