AutoFilter in Excel Add-in VBA (visual Basic for Applications)

AutoFilter in Excel Add-in VBA (visual Basic for Applications)

AutoFilter

Filters data based on criteria supplied by user. In another words, Filter is a process to summarize data based on condition provided by user. In Excel AutoFilter helps user to perform data filter over selected data.

Data source

Above data represents sales information and senior want to see what is the Number of Jimmy made. Here we need to apply filter and following code will make it happen:

Code example

Sub Applyfilter()
    ThisWorkbook.Sheets("Sheet2").Range("A1:G29").AutoFilter
End Sub

Above code Reference Sheet in current workbook over Range “A1” to “G29” and execution will look like:

Output

Here we go!!! we got the filter over given range. Lets put some criteria so we can see how many sales Jimmy has made:

Criteria

Sub ApplyCriteriaFilter()
    ThisWorkbook.Sheets("Sheet2").Range("A1:G29").AutoFilter field:=3, Criteria1:="Jimmy"
End Sub

Parameters

  • Field: It represents the Column Number on which filter needs to be applied
  • Criteria1: It refers to search that we want to see the data on

We can pass multiple criterias based on need as Criteria1, Criteria2 and so on. Lets see how the final output looks like of above code:

Finally it selects Jimmay in filter and gives 8 records out of 28. If you want to clear Criteria simply call ShowAllData method as follow:

ShowAllData/Remove Filter

Sub ShowAllExample()
    ThisWorkbook.Sheets("Sheet2").ShowAllData
End Sub

Hope you find it helpful. Please leave your valuable comments.

Leave a Reply

Your email address will not be published.