Solved

Excel 2012  VBA Named range into an Array

Posted on 2014-09-10
1
387 Views
Last Modified: 2014-09-26
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.
0
Comment
Question by:MrDavidThorn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 51

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 40314394
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

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question