Anyone who has followed my posts on Ozgrid or Chandoo forum will have noticed how frequently I use the autofilter in favour of any kind of loop. I have been lead to believe that in some instances that arrays will perform faster than the humble autofilter however for brevity of code and swiftness I really like the simple elegance of the autofilter. In the article Autofilter on Multiple Conditions I delved into the world of the filer using more than one criteria. It is somewhat convoluted but gets the job done very quickly. Although the Excel VBA coding might be difficult to follow. I have recently discovered that the same problem can be done using a different method.
Here is a simple example of what the recorder might record (with a few simple exceptions).
Sub MoreFilt()
[J10:J24].AutoFilter 1, Array("Freight", "Repair", "Supplies"), 7 'FilterValues
End Sub
The 7 in the VBA code above refers to FilterValues. This is important and needs to be included.
The problem with the 3 criteria, Freight, Repair and Supplies is that they are static. When dealing with data would be nice to create a more dynamic list. The following
Sub FilterMultiRng()
[J10:J24].AutoFilter 1, [transpose(P4:P6)], 7, , 0
End Sub
The beauty of the above is that you can change the length of the cells inside the transpose area so it is a far greater size than will every be necessary and it will still work wonderfully.
[J10:J24].AutoFilter 1, [transpose(P4:P12)], 7, , 0
See how the range has changed to cover P4:P12, if there is only data from P4:P6 Excel will disregard the cells from P7 - 12. This is a way to make your range dynamic without too much trouble. The 0 at the end of the code is used to hide the drop downs on the autofilter.
The Excel file below shows using an example how this VBA autofilter method works.
