Assalamualaikum wr.wb
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.

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.

Name	Value	Description
xlAnd	1	Logical AND of Criteria1 and Criteria2.
xlBottom10Items	4	Lowest-valued items displayed (number of items specified in Criteria1).
xlBottom10Percent	6	Lowest-valued items displayed (percentage specified in Criteria1).
xlFilterCellColor	8	Color of the cell
xlFilterDynamic	11	Dynamic filter
xlFilterFontColor	9	Color of the font
xlFilterIcon	10	Filter icon
xlFilterValues	7	Filter values
xlOr	2	Logical OR of Criteria1 or Criteria2.
xlTop10Items	3	Highest-valued items displayed (number of items specified in Criteria1).
xlTop10Percent	5	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.

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:

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

Criteria1:=">=40"
For instances where the Field:=2 is 40 or more
Criteria1:="="
If you want to display the rows where Field:=2 is blank
Criteria1:="<>"
To display all non-blanks
Field := 1 and the Criteria1:="=B*"
If you want to filter out the names starting with a letter “B”, in the Field:=1
Criteria1:="<>*e*"
To display all names in the first column which do not contain a letter "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 Field:=2  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 Field:=1 is “john” whose Field:= 4 is “Finance.” It is possible to add more fields; the condition being we should not exceed the total column count of headings.

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)

Example 6: Using Autofilter to Filter by Date and Time

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