In case you have any filtered data, it will remove the filters and show you the full dataset.
Excel vba examples print code#
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.
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.