+ Reply to Thread
Results 1 to 6 of 6

Dynamically hide rows for dynamic criteria range of advanced filter

  1. #1
    Registered User
    Join Date
    08-12-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    11

    Dynamically hide rows for dynamic criteria range of advanced filter

    Hi

    I have created a dynamic named range for the criteria of an advanced filter. The dynamic range is needed because I want to allow for entry of between 1 and 6 rows of criteria.

    The criteria will be entered in row 7 to row 12, with the headings in row 6.

    Beside the filter criteria in O7 to O12 is a formula which calculates if the row is empty (returns 0) or has entries in one or more cells (returns 1). The sum of these is stored in cell P4 (ie. count of rows with entries in them)

    The formula for the named range is: =OFFSET('Custom Search & Filter'!$C$6,0,0,1+'Custom Search & Filter'!$P$4,11)

    For the advanced filter to function correctly, users must enter criteria from the top row down. If a row is skipped, but more criteria entered in a row further down (ie. criteria in rows 7 and 9, but nothing in row 8), my dynamic criteria range will include rows 7 and 8, and because 8 is blank, the filter will return all results from the list range of the filter. I don't think people would generally skip rows initially, but might not realise that they can't delete out the criteria from a whole row (unless it is the bottom row).

    To solve this problem, I want to dynamically clear and hide the criteria rows until the visible row as some data in it. Only then will the next row become visible, and be able to be populated with more criteria.

    Hope that makes sense.

    I'm very new to VBA, but here is what I've tried so far. I can't make this run without crashing Excel.

    Please Login or Register  to view this content.


    Can anyone recommend how I can achieve what I'm trying to do?


    Thanks!

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Dynamically hide rows for dynamic criteria range of advanced filter

    Hi Senator,

    Try this:

    Please Login or Register  to view this content.
    Last edited by xladept; 08-24-2013 at 02:54 PM.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    08-12-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Dynamically hide rows for dynamic criteria range of advanced filter

    Thanks xladept

    This looks like a much tidier solution!

    I'm still having some errors though:

    Run-time error '28': Out of stack space

    and sometimes:

    Run-time error '-2147417848 (80010108)': Method "AdvancedFilter' of object 'Range' failed


    With the following changes, I have the functionality I was wanting:

    Please Login or Register  to view this content.

    However, this only works when executed from a button click. If I use the Worksheet_Change way of running the script, it seems to enter a closed loop or something. Just gets stuck flickering the buttons and fails to complete the script.

    The debugger highlights the End If line.

    Thanks again for your help. I really appreciate it. I am very close to having it all working. For now I can use a Button titled "Run Filter" I guess.

    Thanks
    Brent

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Dynamically hide rows for dynamic criteria range of advanced filter

    Hi Brent,

    Please Login or Register  to view this content.
    I don't know what that is, but for the Worksheet_Change event - should it key on a certain column entry?

    Or, would the Worksheet_SelectionChange event be better?

  5. #5
    Registered User
    Join Date
    08-12-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Dynamically hide rows for dynamic criteria range of advanced filter

    Hi xladept

    refresh_datab runs a macro to make the Advanced Filter execute again (so the results of the search are automatically updated)

    this is the code for that macro:

    Please Login or Register  to view this content.
    I tried Worksheet_SelectionChange event earlier, but abandoned it because my code was not working well, and every time I selected a different cell things went crazy.

    I've tried it again just now and it is working better, but still runs the filter every time I select a different cell.

    Is it possible to limit the range of cells that are monitored for change? Ie. Can I make the script run only if cells within the dynamic named range of the advanced filter criteria (or simply A7:K12)?

    Thanks!
    Brent

  6. #6
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Dynamically hide rows for dynamic criteria range of advanced filter

    Maybe:

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Advanced Filter to Dynamic Range
    By John in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-13-2019, 12:06 AM
  2. Advanced Filter Criteria Range Help
    By JoeGio25 in forum Excel General
    Replies: 0
    Last Post: 10-23-2011, 07:34 PM
  3. Advanced filter a dynamic date range
    By oneandoneis2 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-06-2006, 03:57 AM
  4. Advanced filter and Criteria Range
    By gearoid in forum Excel General
    Replies: 2
    Last Post: 07-20-2005, 10:05 AM

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