Advanced Filter (under the Data > Filter menu) is used to show data from a list that meet a certain criteria. The AdvancedFilter method of the Range object can be use to perform the same action from VBA. You can greatly simplify filtering a list for the user by using a macro that does the work on predefined ranges.
This example uses the Orders table from the Northwind.mdb database.
The column headings are copied to row 2, which will be the column heading portion of the Criteria range. Two defined names are set up: fltCriteria for the Criteria range; and fltRange for the range to be filtered. Instead of defining names, you could set these ranged dynamically in the code to account for additional data to filter, or for additional rows in the Criteria range. For this simple example, the ranges are assumed to be static.
The Criteria range is formatted with a yellow interior and borders to provide a visual indication that the user can enter values in it. A button from the Forms toolbar is added that will run the macro to filter.
The user enters criteria in row 3 and clicks the Filter button to filter the range using this macro
Dim rngCrit As Range
Dim rngFilter As Range
Set rngCrit = .Range(“fltCriteria”)
Set rngFilter = .Range(“fltRange”)
If the user want to see only customers whose ID starts with “V” and whose order dates are greater than January 1, 1997, the criteria range would look like this:
To show all the records again, clear the contents of the row 3 and click the Filter button.