Excel Autofill - Fill Down to Bottom of Range

In Excel you may wish to drill down on a range which has a header value not already contained in the rows.  The following article shows a practical example of how to generate a unique row name based on the heading in a specific place.  The procedure is in VBA.

Read More

Excel VBA Filter by Icon Sets

Excel has been improving the autofiltering capabilities and this single topic forms the topic which I have happened upon the most.   I am probably like a lot of developers who had their eyes opened by the Excel loop through a range when you set criteria and Excel does the isolation for you.  The problem with this method occurs when you need to loop through thousands of rows.  This can slow your procedure considerably.  Using the autofilter with VBA by contrast is very quick and the time difference between a small list and a large list is negligible.  More recently Excel has introduced the ability to filter by icon sets.  The conditional formatting coloured arrows or chart indicators which appear in cell.

Read More

Evaluate and Filter Part 2

This post is a follow up from the blog post earlier in the month on Filtering without a filter.  It is an alternative to the autofilter method I have outlined extensively.  The post focuses on filtering out more than one criteria and putting the results in a fresh sheet.  To do so we use the evaluate method.  This is relatively complex, however if we break down what needs to change we can significantly simplify the process.

Read More