Udemy logo

excel data tableExcel 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)







  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



  Dynamic filter



  Color of the font



  Filter icon



  Filter values



  Logical OR of Criteria1 or Criteria2.



  Highest-valued items displayed (number of items specified in Criteria1).



  Highest-valued items displayed (percentage specified in Criteria1).

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

Field := 1 and the Criteria1:="=B*"

 Example 3: Using VBA AutoFilter to Filter out Two Matching Criteria

Sub MultipleCriteria()
With Sheet1
           .AutoFilterMode = False
           .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 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 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.

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

Page Last Updated: March 2014

Excel VBA students also learn

Empower your team. Lead the industry.

Get a subscription to a library of online courses and digital learning tools for your organization with Udemy Business.

Request a demo