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. The following is an example.
Now you can filter based on the iconsets. It is quite simple really. Here is an example
Sub RedDownArrow() 'Red arrow
[a1:a16].AutoFilter 1, ThisWorkbook.IconSets(1).Item(1), xlFilterIcon
End Sub
Sub YellowAcrossArrow() 'Yellow arrow
[a1:a16].AutoFilter 1, ThisWorkbook.IconSets(1).Item(2), xlFilterIcon
End Sub
Sub GreenUpArrow() 'Green arrow
[a1:a16].AutoFilter 1, ThisWorkbook.IconSets(1).Item(3), xlFilterIcon
End Sub
The iconsets(1) relates to the picture of the arrows above. This is specific to this icon set only. If you are trying to adapt this code to a different looking icon set you will need to get the index number which relates to the icon set you are using. The VBA code - item(1) relates to the red down arrow, (2) relates to yellow and (3) relates to green.
The above image is an example of the Excel file attached. The icons in the far right column relate to the VBA code above. The red button naturally filters the Red down arrows and all of the other buttons filter the colour icon relating to the button colour.
The above is another popular icon set. Please keep in mind that you need to change the VBA code if you want to use this icon set.
The following would be an example of the coding for the above.
Sub RedDownArrowV2() 'Red arrow
[a1:a16].AutoFilter 1, ThisWorkbook.IconSets(5).Item(1), xlFilterIcon
End Sub
The iconsets for the above are number 5 .Iconsets(5) is the line which changed.
The attached Excel file shows how this filtering technique with icon sets works. It is a small glimpse into the world of filtering with conditional formatting. A wonderful and very powerful addition to your Excel VBA arsenal.
