PDA

View Full Version : Macro to run advanced filter



edwedw
11-16-2011, 02:44 PM
Please see attached workbook as this isn't easily explained without a visual aid.

Using 'advanced filter' I would like a sub routine to take any data specified on the "filters" worksheet inside the box B5:P26 (which is also duplicated further over) and use this ascriteria for advanced filtering the data on "Customers" current range; A6:AT29 (List range).

The problem I have is that I need the flexibility to potential use all fields for criteria or just one if the others are blank, I cannot use auto-filters as this will be used in excel 2003 where there is a max of 3 criteria and would like this macro to create a friendly UI.

Apologies if I haven't made the problem clear, please ask me to clarify if so.

Any help or advice would be highly apprciated as I've been going around in circuls on this for days.

Many thanks in advance.

mdmackillop
11-16-2011, 03:21 PM
Welcome to VBAX
I've removed your formulae and used a change event to add the criteria to the range. This will only work on one criteria row. If you need multiple lenders etc. this would need refinement.

edwedw
11-16-2011, 03:49 PM
Thank you so much for your response and time mdmackillop. You're right, I will need to adapt this for +1 line per field but this is a huge improvement on the amateurish code I had fiddled together myself.

You are indeed a wizard!

mdmackillop
11-16-2011, 05:56 PM
Consider a Userform for data entry. Here's a simple example.

edwedw
11-17-2011, 10:49 AM
A form to collect the criterion makes much more sense, I have attempted to reprogram for the additional fields but can't seem to get this to work, I'm not sure how to integrate the second list box. :(

mdmackillop
11-17-2011, 02:16 PM
Not a bad attempt.
Use descriptive names for controls (especially if you have a lot of them).
Try to order consistently. It makes it easier to maintain or spot errors.

edwedw
11-25-2011, 01:39 PM
Thank you ever so much for your help, and sorry for the very late reply. I had posed the problem elsewhere before hand with no response. I bow to your knowledge and generosity and hope this example can help others.