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.
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.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
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.
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.
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.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
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.
Reply With Quote
