PDA

View Full Version : Advanced Filter Command Buttons



RobL1982
07-09-2011, 10:49 AM
Hi, I've been experimenting with some command buttons that run the features of Advanced Filter/Clear. I recorded the macro, and clicked the features to save code to store on a command button. I have 2 buttons for each procedure, one for Filter, one for Clear Filter.

This process works well most of the time, but occassionally the search seems to freeze and/or I get an error message to end/debug. Also, the command buttons only seem like they could be built for and searches, and that the or operators do not work. If we include coding for blank rows to cover potential or operators, then the advanced filter button does nothing because it sees a blank row as all records, I believe. Perhaps there could be an if statement to fix this.

My data I'm searching through, not sure if it matters, is external data tables or queries from Access. It consists of material, inventory, and cost. There are class fields, part number fields, description fields, and cost and inventory. I will be searching everything but cost and inventory.

I need some help in getting some code that will not freeze the filter, as well as allow potential or operator searches. I feel that the recording of a macro and applying to the button just isn't the bottom line on what the code should look like. I need a smooth transition to filter once, filter again without clearing, and again and again, and then maybe clear if need be. This keyword filtering is very similar to the system we use at work, and is the reason I need it done this way. I also have folks not interested in auto-filter. I need something similar and requires almost absolutely no learning. I'm almost there! I just need help from someone on writing the correct code. Thanks!

Zack Barresse
07-09-2011, 11:08 AM
Cross-post: http://www.mrexcel.com/forum/showthread.php?t=563146

PLEASE READ THIS BEFORE YOU CROSS-POST AGAIN: http://www.excelguru.ca/node/7