Excel VBA Autofilter

Create Professional Business Reports with a few clicks in Excel:

AutoFilter without Excel VBA

Excel Autofilter is an exCellent tool for analyzing your Data in Microsoft Excel. By using autofilter you are able to filter your Data and analyze / see your Data results very quickly.

Let’s say you want to analyze your Customers and perform sales analysis for your business by using filtering by Customer and state out of your excel Data. Your business have Customers in multiple states and you would like to analyze your sales Data in excel. For example, you can organize your Data in your Excel sheet in three Columns:

1. Your Customers (Customer names or company names) in Column A

2. Your Customer state (location / state of their business such as NY or CA) in Column B and

3. Revenue or Sales Numbers in Column C.

Now, without using Excel VBA / Excel Macros you can do the following:

Select Cell A1 or any Cell within your Data and click on Data > Filter > AutoFilter

Now you can use filter and analyze your Data and see revenues per Customer and / or State.

AutoFilter with Excel VBA

If you want to use Excel VBA or Excel Macros to Autofilter your excel Data you can use the following very simple Excel Macro to do the same sales analysis as you did before without VBA:

Sub AutoFilter()

Range("A1").AutoFilter

End Sub

Running this Macro will apply AutoFilter to your Excel Data. If you run this Excel Macro again it will turn the AutoFilter off. However instead of doing this you can use the following Excel VBA Macro to turn off your Excel AutoFilter:

Sub AutoFilterOff()

ActiveSheet.AutoFilterMode = False

End Sub

After you perform some analysis with your excel data and used different filters if you want to keep the AutoFilter on but at the same time you want to see all your Excel Data use the following Macro:

Sub ShowEverything()

ActiveSheet.ShowAllData

End Sub

You can also use Excel VBA Macro to filter your Excel Data automatically by setting up different filter criterias in your macro. Let’s say you want to see your Customers who are located in California only. In this case you can use the following Excel VBA Macro:

Sub ShowCalifornia()

Selection.AutoFilter Field:=2, Criteria1:="California"

End Sub

Browse our Free Small Business Guide for more tips and articles.