Advanced Filter Excel VBA

Question

I have created a dynamic list range for my advanced filter. I have created name called "Data". When I try to input the name into the VBA formula I receive an error. The dynamic names work for the criteria and the output range?

Range("Sheet2!Data").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
        ("Sheet2!Filter"), CopyToRange:=Range("Sheet2!Location"), Unique:=False

I receive the following run-time error '1004':

Method 'Range' of object'_Global' failed

Shai Rado · Accepted Answer · 2017-02-14 18:15:58Z

3

If your Named Ranges' scope is for Sheet2 only, then you need to fully qualify the Range with Worksheets("Sheet2"), as in the code below:

With Worksheets("Sheet2")
    .Range("Data").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Range("Filter"), _
                CopyToRange:=.Range("Location"), Unique:=False
End With

If your Named Ranges' scope is Workbook then use the code below (there's no need to qualify the Range with the Worksheet):

Range("Data").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("Filter"), _
                CopyToRange:=Range("Location"), Unique:=False

answered Feb 14, 2017 at 18:15

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Diego Ribba Over a year ago

Or

Sheets("Sheet2").Range("Data").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Range("Filter"), CopyToRange:=.Range("Location"), Unique:=False

Jeff Over a year ago

For whatever reason it is not recognizing the name. I created an offset formula for all 3 of the names; Data, Filter, and Location. But code crashes when I uses Data. The range I am using is A11:N1000, but I may add columns and rows, so want to make it dynamic. Could it be possible to have a last row or last column range?

Collectives™ on Stack Overflow

Advanced Filter Excel VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related