"Error 1004 AdvancedFilter Method of Range Class Failed"

Rfriend

Board Regular
Joined
May 10, 2021
Messages
73
Office Version
  1. 2010
Platform
  1. Windows
I am trying to run a User Form I just created and keep getting stuck on this error in my Advanced Filter. I have confirmed the data and range information but can't get past this. The exact same code runs fine in a different user form. Any ideas for a newbie vba user?


Rich (BB code):
Sub AdvFilter()

On Error GoTo errHandler:

With Sheet2
.Range("B8").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheet2.Range("K3:N4"), CopyToRange:=Sheet2.Range("U8:AK8"), Unique:=False
End With

On Error GoTo 0
Exit Sub
errHandler::
MsgBox "An Error has Occurred. Please notify the administrator"
End Sub
 
Last edited by a moderator:

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
As an advanced filter well works only with exact matching headers, no typo allowed !​
And here the destination has more columns than the source, a non sense …​
Try the advanced filter manually and once it works redo the same operations with the Macro Recorder activated in order to get the correct statements.​
 
Upvote 0
Solution
Marc, Thank you for your reply. I am not sure what you mean though. Below is the exact same filter I have running in another workbook and it does run perfectly. The source. criteria, and copy_to_range is identical in both workbooks.

Again, very new and learning.

Sub AdvFilter()
On Error GoTo errHandler:

With Sheet2
.Range("C6").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheet2.Range("O6:R7"), CopyToRange:=Sheet2.Range("T6:AC6"), Unique:=False
End With

On Error GoTo 0
Exit Sub
errHandler::
MsgBox "An Error has Occurred. Please notify the administrator"

End Sub
 
Upvote 0
Something is bad somewhere : in the headers, the data, the criteria, the …​
And what "the administrator" says ?​
 
Upvote 0
I am trying to run a User Form I just created and keep getting stuck on this error in my Advanced Filter. I have confirmed the data and range information but can't get past this. The exact same code runs fine in a different user form. Any ideas for a newbie vba user?


Sub AdvFilter()

On Error GoTo errHandler:

With Sheet2
.Range("B8").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheet2.Range("K3:N4"), CopyToRange:=Sheet2.Range("U8:AK8"), Unique:=False
End With


On Error GoTo 0
Exit Sub
errHandler::
MsgBox "An Error has Occurred. Please notify the administrator"
End Sub

Something is bad somewhere : in the headers, the data, the criteria, the …​
And what "the administrator" says ?​
Thanks, I will keep looking.
 
Upvote 0
The point is that the error may not be in the code. If the headers in K3:N3 or U8:AK8 don't all exactly match headers in the data range, you'd get an error. What does:

Code:
Msgbox Sheet2.Range("B8").currentregion.address

return as a message?
 
Upvote 0
Shhet2.Range ("B8") refers to the column header for "First Name. All three areas of the filter criteria are named identically. The advanced filter shown returns all the data, but not the case with the VBA filter I shared earlier. Please note I moved the criteria and changed the code to reflect the current cell positions accordingly from the earlier code posted.
1620665408198.png


Sub AdvFilter()
With Sheet2
.Range("B8").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheet2.Range("V8:AA9"), CopyToRange:=Sheet2.Range("AC8:AS8"), Unique:=False
End With
On Error GoTo 0
Exit Sub
 

Attachments

  • 1620664988365.png
    1620664988365.png
    62.3 KB · Views: 13
  • 1620665310468.png
    1620665310468.png
    49 KB · Views: 13
Upvote 0
That doesn’t answer my question, which is meant to determine if the code is referring to the correct range. The range in your image appears to include a blank header row so shouldn’t actually work.
 
Upvote 0
I am trying to run a User Form I just created and keep getting stuck on this error in my Advanced Filter. I have confirmed the data and range information but can't get past this. The exact same code runs fine in a different user form. Any ideas for a newbie vba user?


Sub AdvFilter()

On Error GoTo errHandler:

With Sheet2
.Range("B8").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheet2.Range("K3:N4"), CopyToRange:=Sheet2.Range("U8:AK8"), Unique:=False
End With


On Error GoTo 0
Exit Sub
errHandler::
MsgBox "An Error has Occurred. Please notify the administrator"
End Sub

The point is that the error may not be in the code. If the headers in K3:N3 or U8:AK8 don't all exactly match headers in the data range, you'd get an error. What does:

Code:
Msgbox Sheet2.Range("B8").currentregion.address

return as a message?
What is it you wnat me to do with this code?

Revised AdvFlter Code:

Sub AdvFilter()
On Error GoTo errHandler:
With Sheet2
.Range("B8").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheet2.Range("V8:AA9"), CopyToRange:=Sheet2.Range("AC8:AS8"), Unique:=False
End With
Exit Sub
 
Upvote 0
I want you to copy and paste that one line I gave you into the immediate window in the VB Editor and press Enter. The message that appears should confirm the range that the code is trying to filter.
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,249
Members
452,623
Latest member
Techenthusiast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top