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.
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:
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:
Here we go!!! we got the filter over given range. Lets put some criteria so we can see how many sales Jimmy has made:
Sub ApplyCriteriaFilter() ThisWorkbook.Sheets("Sheet2").Range("A1:G29").AutoFilter field:=3, Criteria1:="Jimmy" End Sub
- 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:
Sub ShowAllExample() ThisWorkbook.Sheets("Sheet2").ShowAllData End Sub
Hope you find it helpful. Please leave your valuable comments.