Excel 2012 VBA Named range into an Array

Hi Experts

I have a named range in Excel that values I want to assing to an AutoFilter.

If I records a Macro I get the filter syntax as -

        ActiveSheet.Range("$A$1:$Y$8340").AutoFilter Field:=1, Criteria1:=Array( _
         "A", "B", "C", "D), Operator:=xlFilterValues

So i'm assuiming that I need to replace the Array() in the above syntax with something like

Dim strName() As Variant

strName= Range("FundList").Value


    Sheets("Trades").Select
    ActiveSheet.Range("$A$1:$Y$8340").AutoFilter Field:=1, Criteria1:=Array( _
          strName), Operator:=xlFilterValues

However the above doesnt run.

Any ideas?

The range "Name" is one coloumn of about ten rows of data.
MrDavidThornAsked:
Who is Participating?
 
Rgonzo1971Commented:
Hi,

pls try

Dim varName As Variant
Dim arrName
varName = Range("FundList")
arrName = WorksheetFunction.Index(WorksheetFunction.Transpose(varName), 1, 0)
ActiveSheet.Range("$A$1:$Y$8340").AutoFilter Field:=1, Criteria1:=arrName, _
         Operator:=xlFilterValues

Regards
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.