AutoFilter with an Excel Macro

Add to Favorites

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.



Similar Content on TeachExcel
Rename a Module for an Excel Macro
Tutorial: This Excel tip shows you how to rename a module in Excel. This is a very important thing t...
Add Comments to Cells with an Excel Macro
Macro: Add comments to cells in Excel with this macro. This allows you to quickly and easily add ...
Install a Macro into an Excel Spreadsheet
Tutorial: This tip will show you how to copy an Excel Macro into your workbook or spreadsheet. You w...
Filter Data in Excel - AutoFilter
Macro: This free Excel macro filters data in Excel using the autofilter feature in an Excel macro...
Automatically Run a Macro so Many Seconds, Minutes, or Hours After an Excel Workbook has been Opened
Macro: Run a macro after a certain amount of time has passed since the Excel workbook was ope...
Run a Macro When you Click a Button in Excel
Tutorial: Make a macro run when you click a button in the worksheet in Excel. This allows you to cre...