AutoFilter with an Excel Macro

 

35

This Excel tip shows you how you can use an Excel Macro in order to run a Filter on data within a spreadsheet.
Filtering data in Excel is one of the most important and useful features of Excel. This allows you to very quickly display subsets of data and to view only the desired information. In essence, you are drilling down into the data that you are viewing because you can sort multiple columns and also multiple criteria.
The examples below will show you how to perform a basic filter on one column in a data set, then how to use multiple criteria for one column, and then how to filter a data set based on multiple columns.
AutoFilter in Excel Macros Syntax:
Range("A1").AutoFilter Field:=2, Criteria1:="North"

1. Range - This should be the start of the data set that you need to filter. This is not the actual column for which you want to filter.
2. Field - This is the actual column for which you want to filter. It is the number of columns to the right of the left-most column in the data set - NOT the actual column's number.
3. Criteria - This is the text, number, or character by which you want to filter your data set. It must be enclosed in parenthesis no matter what. You can use all math comparison operators within these parenthesis when dealing with numbers (i.e. >, <, >=, etc.).

Sample Data Set to Filter in Excel:

Col A

Col B

Col C

Salesmen	Region	Sales
Michael Scott	North	58000
Andy Bernard	South	78456
Dwight Schrute	South	56000
Jim Halpert	East	89000
Pam Beesly Halpert	West	34566
Stanley Hudson	West	25678
Phyllis Vance	North	98000
Bart Simpson	East	76000
Judge Dredd	North	66000

Filter based on Region - The code below will autofilter to only show salesmen from the North region.

Sub AutoFilter_in_Excel()

Range("A1").AutoFilter Field:=2, Criteria1:="North"

End Sub

Filter the data set to display salesmen from the "North" or the "South" regions, which means salesmen from both regions will be displayed. Notice this uses the "xlor" operator.

Sub AutoFilter_in_Excel()

Range("A1").AutoFilter Field:=2, Criteria1:="North", Operator:= xlor, Criteria2:="South"

End Sub

Filter the data set to display salesmen which have sales that are above $50,000 AND below $75,000. This uses the "xland" operator.

Sub AutoFilter_in_Excel()

Range("A1").AutoFilter Field:=3, Criteria1:=">50000", Operator:= xland, Criteria2:="<75000"

End Sub

This autofilter macro will filter the data set to show all salesmen from the "North" region that have sales over $75,000.

Sub AutoFilter_in_Excel()

Range("A1").AutoFilter Field:=2, Criteria1:="North"

Range("A1").AutoFilter Field:=3, Criteria1:=">75000"

End Sub

Note: For all of the above examples you should pay close attention to the subtle differences. For instance, the last example runs two autofilter commands in Excel but they are run on different Fields.

Like this tutorial? Follow us on Google +