#Excel vba tutorial microsoft full#
#Excel vba tutorial microsoft code#
#Excel vba tutorial microsoft download#

This tells VBA to use both the criteria and filter the data if any of the two criteria are met. Note that here I have used the xlOR operator. Worksheets('Sheet1').Range('A1').AutoFilter Field:=2, Criteria1:='Printer', Operator:=xlOr, Criteria2:='Projector'

#Excel vba tutorial microsoft code#

The below code would do this: Sub FilterRowsOR() Suppose I have the same dataset, and this time I want to filter all the records where the item is either ‘Printer’ or ‘Projector’. Example: Multiple Criteria (AND/OR) in the Same Column

#Excel vba tutorial microsoft download#

If this is all you want to do, better used the inbuilt Filter functionality.īut as you read the remaining tutorial, you’ll see that this can be combined with some extra code to create powerful automation.īut before I show you those, let me first cover a few examples to show you what all the AutoFilter method can do.Ĭlick here to download the example file and follow along. This can easily be done using inbuilt filter functionality. Now if you’re thinking – why do I need to do this using a VBA code. Note that here we have used Field:=2, as the item column is the second column in our dataset from the left. The above code refers to Sheet1 and within it, it refers to A1 (which is a cell in the dataset). Worksheets('Sheet1').Range('A1').AutoFilter Field:=2, Criteria1:='Printer' The below code would filter all the rows where the item is ‘Printer’. Suppose you have a dataset as shown below and you want to filter it based on the ‘Item’ column. Example: Filtering Data based on a Text condition Now let’s see some examples of using Excel VBA Autofilter that will make it’s usage clear.

#Excel vba tutorial microsoft full#

In case you have any filtered data, it will remove the filters and show you the full dataset. This simply means that if you can not see the filter icons in the column headers, you will start seeing it when this above code is executed, and if you can see it, then it will be removed. The above code would simply apply the Autofilter method to the columns (or if it’s already applied, it will remove it). Worksheets('Filter Data').Range('A1').AutoFilter In case you don’t use any argument, it would simply apply or remove the filter icons to the columns. This argument can be TRUE or FALSE.Īpart from Expression, all the other arguments are optional.

VisibleDropDown: You can specify whether you want the filter drop-down icon to appear in the filtered columns or not.

Criteria2: This is the second criteria on which you can filter the dataset.

The following operators are available for use: xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues

Operator: In case you’re using criteria 2 as well, you can combine these two criteria based on the Operator.

Criteria1: This is the criteria based on which you want to filter the dataset.

So if you want to filter data based on the second column, this value would be 2. This is counted from the left in the dataset.

Field: This is the column number that you want to filter.

Expression: This is the range on which you want to apply the auto filter.

AutoFilter( _Field_, _Criteria1_, _Operator_, _Criteria2_, _VisibleDropDown_ )

Example: Filter Data based on a Cell ValueĮxcel VBA Autofilter Syntax Expression.

Example: Copy Filtered Rows into a New Sheet.

Example: Using Wildcard Characters in Autofilter.

Example: Filter Top 10 Percent Using the AutoFilter Method.

Example: Filter Top 10 Records Using the AutoFilter Method.

Example: Multiple Criteria With Different Columns.

Example: Multiple Criteria (AND/OR) in the Same Column.

Example: Filtering Data based on a Text condition.

Note: I will cover this example (on filtering data based on a drop-down selection and copying into a new sheet) later in this tutorial. In such a scenario, using VBA Autofilter can speed things up and save time. While this can be done using the inbuilt filter functionality along with some copy-paste, it can take you a lot of time to do this manually. You should use VBA Autofilter when you want to filter the data as a part of your automation (or if it helps you save time by making it faster to filter the data).įor example, suppose you want to quickly filter the data based on a drop-down selection, and then copy this filtered data into a new worksheet. If you just need to filter data and do some basic stuff, I would recommend stick to the inbuilt Filter functionality that Excel interface offers. If you have a dataset and you want to filter it using a criterion, you can easily do it using the Filter option in the Data ribbon.Īnd if you want a more advanced version of it, there is an advanced filter in Excel as well.

A lot of Excel functionalities are also available to be used in VBA – and the Autofilter method is one such functionality.