My Blog

Filtering a Range – Adding AutoFilter

In continuation from here

Sometime you need to just add Autofilter in your data so that you can filter the data based on multiple conditions. In short it lets you customize you filtering criteria.

See this image.

The above can be achieved by this code:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    '~~> Add a New Workbook
    xlWorkBook = xlApp.Workbooks.Add

    '~~> Display Excel
    xlApp.Visible = True

    '~~> Set the relebant sheet that we want to work with
    xlWorkSheet = xlWorkBook.Sheets("Sheet1")

    With xlWorkSheet
        '~~> Directly type the values that we want
        .Range("A1").Value = "Month" : .Range("B1").Value = "Date" : .Range("C1").Value = "Money Spent"
        .Range("A2:A5").Value = "January" : .Range("A6:A9").Value = "February"
        .Range("A10:A13").Value = "March" : .Range("A14:A17").Value = "April"

        .Range("B2").Value = "1/6/2011" : .Range("B3").Value = "1/10/2011" : .Range("B4").Value = "1/20/2011"
        .Range("B5").Value = "1/31/2011" : .Range("B6").Value = "1/6/2011" : .Range("B7").Value = "1/10/2011"
        .Range("B8").Value = "1/20/2011" : .Range("B9").Value = "1/31/2011" : .Range("B10").Value = "1/6/2011"
        .Range("B11").Value = "1/10/2011" : .Range("B12").Value = "1/20/2011" : .Range("B13").Value = "1/31/2011"
        .Range("B14").Value = "1/6/2011" : .Range("B15").Value = "1/10/2011" : .Range("B16").Value = "1/20/2011"
        .Range("B17").Value = "1/31/2011"

        .Range("C2").Value = "200" : .Range("C3").Value = "350" : .Range("C4").Value = "155"
        .Range("C5").Value = "345" : .Range("C6").Value = "213" : .Range("C7").Value = "231"
        .Range("C8").Value = "321" : .Range("C9").Value = "341" : .Range("C10").Value = "231"
        .Range("C11").Value = "345" : .Range("C12").Value = "432" : .Range("C13").Value = "124"
        .Range("C14").Value = "543" : .Range("C15").Value = "342" : .Range("C16").Value = "222"
        .Range("C17").Value = "215"

        '~~> Add Autofilter to the header range
        .Range("A1:C17").AutoFilter(Field:=1, Operator:=Excel.XlAutoFilterOperator.xlFilterValues)

        '~~> Autofitting text in columns
        .Columns("A:C").EntireColumn.AutoFit()
    End With
End Sub

If you want you can also specify the filter options in your code to directly filter the data. For example, if you want to show only records form “January” then see this code below.

Change the line:

'~~> Add Autofilter to the header range
.Range("A1:C17").AutoFilter(Field:=1, Operator:=Excel.XlAutoFilterOperator.xlFilterValues)

to

'~~> Add Autofilter to the header range
.Range("A1:C17").AutoFilter(Field:=1, Criteria1:="January", Operator:=Excel.XlAutoFilterOperator.xlFilterValues)

And you will get this as your result.

You can also filter it to show data from “January” where the “Money Spent” is less than say 300. For that you will change the code to this.

'~~> Add Autofilter to the header range
.Range("A1:C17").AutoFilter(Field:=1, Criteria1:="January", Operator:=Excel.XlAutoFilterOperator.xlFilterValues)
.Range("A1:C17").AutoFilter(Field:=3, Criteria1:="<300", Operator:=Excel.XlAutoFilterOperator.xlFilterValues)

This is the result that you will get.