In Excel VBA it is possible to apply a filter directly to items contained within an array. This method, little known, is a way of filtering without the more traditional auto filter. I have not conducted any tests but as the filter is performed in memory I assume the process happens more quickly than a regular Excel auto filter. This would make a nice Excel case study.
Here is a simple example of the Excel VBA method using a one dimensional array.
Option Explicit
Sub OneDFilter() 'Excel VBA 1D arry to filter.
Dim ar As Variant
Dim var As Variant
ar = Array("Banana", "Pear", "Apple", "Apple", "Banana", "Orange")
var = Filter(ar, "Apple", 0) 'Apple is the criteria
End Sub
The result is as follows:
Var = "Banana", "Pear", "Banana", "Orange"
The following VBA coding is a little more practical as it turns a 2 dimensional array (such as a cell reference) into a 1 dimensional array, then filters the 1D array. So the technique is based on a range, as I said it is far more practical.
Option Explicit
Sub FilterWithCode() 'Excel VBA to filter an array
Dim ar As Variant
Dim var As Variant
ar = [A2:A15]
ar = Application.Transpose(ar)
var = Filter(ar, "Apple", 0) 'Apple is the criteria to remove
Range("G1", Range("G" & UBound(var) + 1)).Value = Application.Transpose(var)
End Sub
The application.transpose line is repeated from the article on filtering multiple items with an Excel filter. That article Autofilter on Multiple Conditions is along the same lines as the filter inside an array.
More on this very useful technique can be found on SNB's website which has some insightful coding on a range of complex topics.
