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!

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

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