VBA Filter Function returns a subset of a string array based on criteria. You can use this Function with zero-based array for easily extracting the matched items from a string array. Let’s see the Syntax and Examples on Filter Function in VBA.
Learn how to effectively use the VBA Filter function to extract specific data from arrays or ranges in Excel. Explore real-time examples and step-by-step code explanations to streamline your data processing tasks and enhance your VBA programming skills.
VBA Filter Syntax:
Below is the Syntax of the VBA Filter Function. It takes Source Array, Match Criteria as required parameters. You can return the Matched or Un matched items by setting the Include perimeter. We can also set the Caparison type to match in each item of the array.
Filter(SourceArray, Match, [Include], [Compare])
- SourceArray is a required parameter that specifies the array of strings to be searched.
- Match is a required parameter that specifies the string to search for within each element of the supplied SourceArray.
- Include is an optional argument that specifies whether the returned array should consist of elements that are matched with Criteria. True is the default and returns the items contains the matched string and False returns the items that does not contains the match string.
- Compare is an optional argument that specifies the type of String comparison (vbUseCompareOption,vbBinaryCompare,vbTextCompare,vbDatabaseCompare) to make. vbBinaryCompare is the default Comparison.
Following VBA Code show you how to use VBA Filter Function and Its syntax. It takes string array of Names and Filters the items that Matches letter ‘a’. You can pass True/False to include or exclude the filtered items as shown below procedure:
Sub sbAT_VBA_Filter_Function()
'Declare an array variable
Dim myStringsArray As Variant
'Create a string array
myStringsArray = Array("Ravi", "Mike", "Allen", "Tom", "Jenny", "James")
'Another Variable to Store Filtered Array Items
Dim myStringsArray_Filtered As Variant
'Filter function: To Filter String array items that contains "a"
myStringsArray_Filtered = Filter(myStringsArray, "a")
'To Filter the NOT contains "a"-------------
'myStringsArray_FilteredFalse = Filter(myStringsArray, "a", False)
End Sub
VBA Filter Examples:
Here the most useful examples on VBA Filter Function. It is very handy while dealing with zero based arrays. We can quickly filter the items with matched items. Let us see the verity of the examples using VBA Filter Functions.
1. Filter an array for matches
VBA Example Code to Filter an array for matches. The below code have the array of names and returns the matched items of in the array:
Sub FilterByValue_FilterAnAarrayForMatches()
Dim SourceArray As Variant
Dim Match As String
Dim Include As Boolean
Dim Compare As Integer
' Set the values of the variables
SourceArray = Array("Olivia", "Emma", "Ava", "Sophia", "Isabella", "Mia", "Charlotte", "Amelia", "Abigail", "Evelyn", "Liam", "Noah", "William", "James", "Oliver", "Elijah", "Benjamin", "Lucas", "Henry", "Alexander")
Match = "a"
Include = True
Compare = vbTextCompare
' Use the Filter function to return a subset of the array
Dim FilteredArray As Variant
FilteredArray = Filter(SourceArray, Match, Include, Compare)
MsgBox UBound(FilteredArray, 1) & " Items: " & vbCr & vbCr & Join(FilteredArray, vbCr)
End Sub
2. Filter an array for non-matches.
VBA Example Code to Filter an array for Non-matches. The below excludes the matched items of in the array and returns the non-matched items:
Sub FilterByValue_FilterAnAarrayForNon_Matches()
Dim SourceArray As Variant
Dim Match As String
Dim Include As Boolean
Dim Compare As Integer
' Set the values of the variables
SourceArray = Array("Olivia", "Emma", "Ava", "Sophia", "Isabella", "Mia", "Charlotte", "Amelia", "Abigail", "Evelyn", "Liam", "Noah", "William", "James", "Oliver", "Elijah", "Benjamin", "Lucas", "Henry", "Alexander")
Match = "ia"
Include = False
Compare = vbTextCompare
' Use the Filter function to return a subset of the array
Dim FilteredArray As Variant
FilteredArray = Filter(SourceArray, Match, Include, Compare)
MsgBox UBound(FilteredArray, 1) & " Items: " & vbCr & vbCr & Join(FilteredArray, vbCr)
End Sub
3. Filtering Data Based on a Specific Value:
Following Example shows you how to filter the array based on the specific Value. This will check the Departments that contains the matched string.
'VBA Code for Filtering Data Based on a Specific Value:
Sub FilterByValue_SpecificValue()
Dim SourceArray As Variant
Dim Match As String
Dim Include As Boolean
Dim Compare As Integer
' Set the values of the variables
SourceArray = Array("IT", "HR", "Finance", "Sales", "Marketing", "Operations", "Research", "Customer Service", "Production", "Quality Assurance")
Match = "i"
Include = True
Compare = vbTextCompare
' Use the Filter function to return a subset of the array
Dim FilteredArray As Variant
FilteredArray = Filter(SourceArray, Match, Include, Compare)
MsgBox UBound(FilteredArray, 1) & " Items: " & vbCr & vbCr & Join(FilteredArray, vbCr)
End Sub
4. Filtering Codes based on a Specific Code:
Following Example shows you how to filter the array based on the specific Code. This will check the Array of Codes and returns the codes that contains the matched code.
Sub FilterByValue_StateCodes()
Dim SourceArray As Variant
Dim Match As String
Dim Include As Boolean
Dim Compare As Integer
' Set the values of the variables
SourceArray = Array("AL", "AK", "AZ", "AR", "CA", "CO", "CT", "DE", "FL", "GA", "HI", "ID", "IL", "IN", "IA", "KS", "KY", "LA", "ME", "MD", "MA", "MI", "MN", "MS", "MO", "MT", "NE", "NV", "NH", "NJ", "NM", "NY", "NC", "ND", "OH", "OK", "OR", "PA", "RI", "SC", "SD", "TN", "TX", "UT", "VT", "VA", "WA", "WV", "WI", "WY")
Match = "A"
Include = True
Compare = vbTextCompare
' Use the Filter function to return a subset of the array
Dim FilteredArray As Variant
FilteredArray = Filter(SourceArray, Match, Include, Compare)
MsgBox UBound(FilteredArray, 1) & " Items: " & vbCr & vbCr & Join(FilteredArray, vbCr)
End Sub
5. Filtering based on multiple criteria:
You can use the following VBA Code to Filter the Array based on multiple Criteria.
Sub FilterExample_MultipleCriteria1()
Dim SourceArray As Variant
Dim criteria As String
Dim FilteredArray As Variant
' Set the values of the variables
SourceArray = Array("IT", "HR", "Finance", "Sales", "Marketing", "Operations", "Research", "Customer Service", "Production", "Quality Assurance")
arrCriteria = Array("a", "s")
' Use the FilterArray function to return a subset of the array
FilteredArray = SourceArray
For Each item In arrCriteria
FilteredArray = Filter(FilteredArray, item)
Next
MsgBox UBound(FilteredArray, 1) & " Items: " & vbCr & vbCr & Join(FilteredArray, vbCr)
End Sub
6. Filtering a Range with criteria:
Let us Assume that you have list of states in Range A1:A50 and You wants to filter the States that contains specific string. Here is the VBA Code to filter the Range based on a criteria using VBA Filter function.
Sub sbAT_FilterStatesMatchingStringa()
Dim myStringsArray_Filtered As Variant
strMatchString = "New"
rngArr = fnRangeToArray(Range("A1:A50"))
FilteredArray = Filter(rngArr, strMatchString)
MsgBox UBound(FilteredArray, 1) & " Items: " & vbCr & vbCr & Join(FilteredArray, vbCr)
'You Enter the Filtered data back to any Range
Range("B2:B100").ClearContents
Range("B2").Resize(UBound(FilteredArray, 1) + 1, 1).Value = Application.Transpose(FilteredArray)
End Sub
VBA Filter Function – Example Workbook
Here is the Workbook with VBA Filter Function Examples. This file will help you to explore the code and understand the function with real-time example codes.
Final Thoughts:
In this blog post, we delved into the VBA Filter function and explored its application in data processing and analysis. The Filter function serves as a valuable tool for extracting specific data from arrays or ranges based on user-defined criteria. By utilizing this function, you can efficiently manipulate and analyze large datasets in Excel.
We commend you to use the AutoFilter method or define Custom Function for the Multiple Criteria. Since the default VBA function deals with only zero-based arrays and very limited comparisons.
By mastering the Filter function in VBA, you can streamline your data processing workflows, saving time and effort. Whether you need to filter data by department, values, or partial matches, the Filter function offers a versatile solution.
Remember to adapt the code examples to fit your specific data table and requirements. Experiment with different criteria and explore the vast possibilities of data filtering in VBA. With practice, you can elevate your Excel skills and leverage the power of VBA to efficiently analyze and manipulate data.
We value your feedback! Did you find this blog post helpful in understanding Filter function in VBA? We would love to hear about your experience and any suggestions you may have. Please take a moment to share your thoughts in the comments section below. Your feedback will enable us to create even better content tailored to your needs. Thank you for reading, and we look forward to hearing from you! Please let us know if you want us to add any other Examples which helps VBA developers.
Start using the Filter function in VBA today and unlock a new level of data processing and analysis capabilities in Excel.
