Error when filtering after attempting to create an array in Excel with vba

Question

I'm attempting to take a list of names and filter another list based on whether these names appear. To do so, I want to create an array of the names and then take this array to apply an autofilter to the other sheet from a specific column. Here's the code I currently have:

Dim AdvisorEINs As Variant

    AdvisorEINs = Worksheets("Names to Filter").Range("A1:A36").Value
    Worksheets("All Advisors").Range("$A$1:$DZ$2216").AutoFilter Field:=15, Criteria1:=AdvisorEINs.Value, Operator:=xlFilterValues

Currently this is returning Run-time error '424': Object required when attempting to run and debugging is highlighting the filtering line. I've added in a Debug.Print on the AdvisorEINs variable, which outputs the same error message and suggests to me that this is due to variable not picking up data correctly. I'm not certain how to have the array pick up the data any other way - is someone able to advise on this?

AdvisorEINs is an array of values. Criteria1:=AdvisorEINs.Value treats it like a range. So there is a datatype error in this assignment. It should be Criteria1:=AdvisorEINs. But beyond that, shouldn't Criteria1 be a single value? Is it possible to assign an array to it? — Variatus
– Variatus, Commented Apr 9, 2020 at 10:38
@Variatus, yes, provided you specify Operator:=xlFilterValues, as OP's doing — HTH
– HTH, Commented Apr 9, 2020 at 10:45

HTH · Accepted Answer · 2020-04-09 10:50:35Z

2

you have to

use AdvisorEINs instead of AdvisorEINs.Value
transpose the array , because the Value property of a multicell range returns a 2D array (though with one column only... )

like follows:

Dim AdvisorEINs As Variant

AdvisorEINs = Worksheets("Names to Filter").Range("A1:A36").Value
Worksheets("All Advisors").Range("$A$1:$DZ$2216").AutoFilter Field:=15, Criteria1:=Application.Transpose(AdvisorEINs), Operator:=xlFilterValues

answered Apr 9, 2020 at 10:50

HTH

2,0311 gold badge6 silver badges10 bronze badges

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

Collectives™ on Stack Overflow

Error when filtering after attempting to create an array in Excel with vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related