+ Reply to Thread
Results 1 to 2 of 2

Name the results of a filter

  1. #1
    Registered User
    Join Date
    04-30-2010
    Location
    Monterey, CA
    MS-Off Ver
    Excel 2003
    Posts
    1

    Question Name the results of a filter

    I've been looking everywhere for this answer with no luck, so props to whomever can tackle this!

    What I need is the code for naming a range that's been filtered. For example, in my macro I'm telling it to filter like so:

    Selection.AutoFilter Field:=2, Criteria1:="320"

    Now, what I need to do is name that data to "ThreeTwenty." When recording this macro, visually, it comes out like this:

    ActiveWorkbook.Names.Add Name:="ThreeTwenty", RefersToR1C1:= _
    "=Data!R85C4:R144C10"


    The problem I'm running into here is that the "RefersToR1C1" coordinates will likely change each month, so is there a way for it to determine its own dimensions for that filtered range and/or is there some other cool trick to accomplish the same thing that I'm not currently aware of? Also as a note, the data being filtered starts in B4 (the filter headers start on row 3). Please respond with code, so I can copy and past exactly what you say to see if it works - been working on this for days and everything I've tried seems to fail. Thanks in advance!

  2. #2
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Name the results of a filter

    IMO, you will better served by using Advanced Filter using its option to copy the results to a new location. Then you could dimension a variable to dynamically find the last row of the filtered range and create a named range.

    After creating the code for the Advanced Filter, the code for creating the named range would look something like this. Obviously, adjust sheet references and ranges as required per your filtered results.

    Please Login or Register  to view this content.
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1