+ Reply to Thread
Results 1 to 4 of 4

Advanced filter: how to use criteria range of variable size

  1. #1
    Registered User
    Join Date
    06-25-2010
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    3

    Advanced filter: how to use criteria range of variable size

    Hi,

    I am working on a timetable which has several groups of tasks (e.g. month-end, budget etc).

    The users want to see only those groups of tasks that concern them. To do that, I have a form in the first tab where the users select the groups of tasks that they want to see. Then they run a macro with advanced filter and see the filtered timetable in the next tab. The macro I recorded is very simple:

    Please Login or Register  to view this content.


    However the filtering does not work the way I want it to. The problem is (I think) that I have to tell Excel the precise size of the criteria range for the filtering to work correctly.

    However in my case the number of rows in the criteria field is not fixed. It changes depending on how many groups of tasks the users will select (up to 15). I do not know how to change the macro so that would provide under "criteria" the exact criteria range (or at least the number of rows in that range) that will results from the selection of users (if you look into the file, the range can be anything between D5 and D5:D18). In the file you'll see how as a workaround to using VBA I tried to define name "criteria" as formula that calculates the address of the criteria range but of course it does not work

    I suppose this is not a problem for someone who knows VBA so I thought I'll ask the experts. The attachment will hopefully give some idea about what I'm trying to do.
    I will be very grateful for any help, also if there is a solution that does not require VBA
    (And if I could get help over this weekend, it would be really great!)

    Cheers!
    Attached Files Attached Files
    Last edited by AnnaGb; 06-26-2010 at 08:07 PM. Reason: solved

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Advanced filter: how to use criteria range of variable size

    Dynamic named ranges:

    http://support.microsoft.com/kb/830287

    http://www.contextures.com/xlnames01.html
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    06-25-2010
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    3

    Question Re: Advanced filter: how to use criteria range of variable size

    Thank you for the links. They do seem to work for naming dynamic ranges but do not work in this case for advanced sorting. More precisely, they work once but it looks like after running the macro and sorting, Excel overwrites the formula with the address of criteria range used - so the range is not dynamic any more.

    Back to square one

  4. #4
    Registered User
    Join Date
    06-25-2010
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Advanced filter: how to use criteria range of variable size

    I think I found a solution: I recorded a macro that adds the dynamic named range and added it to the original macro. It looks like it is now working well (although I need to do more testing)

    Please Login or Register  to view this content.


    Many thanks for help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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