Excel VBA Autofilter: How to Use Filters to Highlight Your Data

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)

Where

• 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.

• 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

Criteria1:=">=40"

• If you want to display the rows where E-age is blank, the code looks like this

Criteria1:="="

•  To display all non-blanks we use

Criteria1:="<>"

•  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

Criteria1:="<>*e*"

•   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.