Premium Excel Course Now Available!
Build Professional - Unbreakable - Forms in Excel
45 Tutorials - 5+ Hours - Downloadable Excel Files
Instant Access! - Lifetime Access!
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"
- 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.
- 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.
- 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:
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.
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...
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
Macro: This free Excel macro illustrates all of the possible parameters and arguments that yo...