Excel VBA (Visual Basic for Application) is a powerful programming tool integrated with MS office suite. VBA has many constructs and methods that can be applied to manipulate data in an Excel worksheet (you can look up our introductory VBA tutorial to get a feel of all that VBA can do for you). AutoFilter in VBA is an important method that gives you the capability to filter worksheets and cells to selectively choose data.
Today, we will walk you through the AutoFilter in VBA. If you are new to VBA, we suggest that you go through our basic Excel VBA tutorial.
AutoFilter is applicable to a column or a set of columns. It filters data depending on the given criteria. The syntax of Autofilter looks like this
expression .AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown)
- Field- It is an integer offset of the field which contains the filter. The data type is variant, which means it can hold any data types – integers, strings, date and so on.
- Criteria1- It’s a condition based on which columns are selected.
- Operator- It specifies the type of filter. Some of the operators commonly used in Excel VBA programming are listed below.
Logical AND of Criteria1 and Criteria2.
Lowest-valued items displayed (number of items specified in Criteria1).
Lowest-valued items displayed (percentage specified in Criteria1).
Color of the cell
Color of the font
Logical OR of Criteria1 or Criteria2.
Highest-valued items displayed (number of items specified in Criteria1).
Highest-valued items displayed (percentage specified in Criteria1).
- Criteria 2- This is the secondary condition based on which columns are selected. It’s combined with criteria1 and operator to create a compound criteria.
- VisibleDropDown- It’s true by default. It’s of data type variant. If it’s true then, the Autofilter dropDropDown arrow for the filtered field is displayed. If false, the dropDropDown arrow is hidden.
Now that you’re familiar with the concept and syntax of AutoFilter, lets move on to a few simple and practical exercises. Feel free to refer back to our VBA macros course at any point for more details.
Example 1: To Close All Existing AutoFilters and Create New AutoFilters
Sub AutoFilter1() With ActiveSheet .AutoFilterMode = False .Range("A1:E1").AutoFilter End With End Sub
In this program .AutoFilterMode = false turns off any existing AutoFilters. Whereas .Range(“A1:E1”).AutoFilter creates an AutoFilter which is applicable to the range A1:E1 of the active worksheet.
From here on, we will reference a worksheet which has headings in the range A1: D1 and data in the range A1:D50. The headings are as follows:
EmployeeName| E-age|Date of Joining| Department
Example 2: Using AutoFilter to match single criteria
Sub FilterTo1Criteria() With Sheet1 .AutoFilterMode = False .Range("A1:D1").AutoFilter .Range("A1:D1").AutoFilter Field:=2, Criteria1:=40 End With End Sub
This is a simple program which extracts rows where the age of the employees is 40. The “Field” value is 2 which means it refers to the second column which is ” E-age.” Criteria is that the values in column 2 should be equal to 40. Let’s take a look at the various types of criteria that you can include in your programs.
- For instances where the E-age is 40 or more, you can use the following code
- If you want to display the rows where E-age is blank, the code looks like this
- To display all non-blanks we use
- If you want to filter out the names starting with a letter “B”, in the Employee name field, then you have to assign the
Field := 1 and the Criteria1:="=B*"
- To display all names in the first column which do not contain a letter “e”, use the code
- If you want to hide the filter arrow then set VisibleDropDown:=False. This is the next argument after Criteria1
Example 3: Using VBA AutoFilter to Filter out Two Matching Criteria
Sub MultipleCriteria() With Sheet1 .AutoFilterMode = False .Range("A1:D1").AutoFilter .Range("A1:D1").AutoFilter Field:=2, Criteria1:=">=30", _ Operator:=xlAnd, Criteria2:="<=40" End With End Sub
In this program, we have specified two criteria. The operator used is the “logical And” for the 2 criteria. Thus, only those records are selected where “e-age” is “>= 30” and “<=40.”
Example 4: Using Autofilter on two different fields
Sub Filter2Fields() With Sheet1 .AutoFilterMode = False With .Range("A1:D1") .AutoFilter .AutoFilter Field:=1, Criteria1:="John" .AutoFilter Field:=4, Criteria1:="Finance" End With End With End Sub
In this program, we have selected records where Employee Name is “john” whose department is “Finance.” It is possible to add more fields; the condition being we should not exceed the total column count of headings, i.e. four.
Using Dates in AutoFilter
MS Excel uses the US date format. We recommend you to change your Date settings to this format. Else you have to use the DateSerial(). The syntax looks like this
DateSerial(year, month, day)
Let’s take a look at an example that uses the data type Date to filter columns.
Example 5: Program to Filter by Date
Sub FilterDate1() Dim Date1 As Date Dim str_Date As String Dim l_Date As Long Date1 = DateSerial(2010, 12, 1) l_Date = Date1 Range("A1").AutoFilter Range("A1").AutoFilter Field:=1, Criteria1:=">" & l_Date End Sub
In this program, we declare Date1 as variable of type date, str_Date as variable of type string and l_Date of variable of type long. DateSerial() function converts the date passed to it into US date format. We use Autofilter to display records more recent than the given date (1/12/2010).
Using TimeSerial Function along with VBA Autofilter
TimeSerial() function returns the time in hours, minutes and seconds. The syntax looks like this
TimeSerial(hour, minute, second)
Let’s take a close look at the parameters to understand them better. All the three parameters require integer data type.
- Hour: any number between 0 and 23 inclusive or a numeric expression.
- Minute: any numeric expression.
- Second: any numeric expression.
TimeSerial(17, 28, 20) will return the serial representation of 5: 28:20 PM. TimeSerial() can be used along with the DateSerial() to return the exact time and date in a VBA program.
Example 5: Using Autofilter to Filter by Date and Time
Sub FilterDateTime() Dim d_Date As Date Dim db_Date As Double If IsDate(Range("B1")) Then db_Date = Range("B1") db_Date = DateSerial(Year(db_Date), Month(db_Date), Day(db_Date)) + _ TimeSerial(Hour(db_Date), Minute(db_Date), Second(db_Date)) Range("A1").AutoFilter Range("A1").AutoFilter Field:=1, Criteria1:=">" & db_Date End If End Sub
In this program IsDate() is used to see whether the cell contains an expression that can be converted into a date. Then the content of the cell is assigned to the db_Date variable. Next DateSerial() and TimeSerail () are combined and the result assigned to db_Date. We filter the records using Field 1 as criteria to return dates greater than db_Date.
Excel VBA is an exciting programming area. It equips you with features and functionalities to develop simple and efficient code. AutoFilter is central to Excel VBA programming. Leverage it to provide different views of the data. MrExcel shows some neat tricks in this VBA course, that can help you out. Once you’re ready to tackle more advanced usage, you can do so with our Ultimate VBA course.