{"id":3256,"date":"2021-04-27T10:11:38","date_gmt":"2021-04-27T17:11:38","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=3256"},"modified":"2021-04-27T10:11:40","modified_gmt":"2021-04-27T17:11:40","slug":"how-to-use-excel-vba-autofilter-a-complete-guide-with-examples","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=3256","title":{"rendered":"How to Use Excel VBA Autofilter: A Complete Guide with Examples"},"content":{"rendered":"\n<p>A lot of Excel functionalities are also available to be used in VBA \u2013 and the&nbsp;<strong>Autofilter&nbsp;<\/strong>method&nbsp;is one such functionality.<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><img decoding=\"async\" src=\"https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/AutoFilter-icon-the-Excel-Data-Ribbon.png\" alt=\"AutoFilter icon the Excel Data Ribbon\" class=\"wp-image-26894\"\/><\/figure><\/div>\n\n\n\n<p>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.<\/p>\n\n\n\n<p>And if you want a more advanced version of it, there is an&nbsp;advanced filter&nbsp;in Excel as well.<\/p>\n\n\n\n<p><strong>Then Why Even Use the AutoFilter in VBA?<\/strong><\/p>\n\n\n\n<p>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.<\/p>\n\n\n\n<p>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).https:\/\/b22196cd5e66bbcddc8025280cf76bdd.safeframe.googlesyndication.com\/safeframe\/1-0-38\/html\/container.html<\/p>\n\n\n\n<p>For 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.<\/p>\n\n\n\n<p>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.<\/p>\n\n\n\n<p>In such a scenario, using VBA Autofilter can speed things up and save time.<\/p>\n\n\n\n<p><strong>Note<\/strong>: I will cover this example (on filtering data based on a drop-down selection and copying into a new sheet) later in this tutorial.<\/p>\n\n\n\n<p><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Excel VBA Autofilter Syntax<\/h2>\n\n\n\n<pre class=\"wp-block-preformatted\">Expression. AutoFilter( _Field_ , _Criteria1_ , _Operator_ , _Criteria2_ , _VisibleDropDown_ )<\/pre>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>Expression<\/strong>: This is the range on which you want to apply the auto filter.<\/li><li><strong>Field<\/strong>:&nbsp;<em>[Optional argument]<\/em>&nbsp;This is the column number that you want to filter. This is counted from the left in the dataset. So if you want to filter data based on the second column, this value would be 2.<\/li><li><strong>Criteria1<\/strong>:&nbsp;<em>[Optional argument]<\/em>&nbsp;This is the criteria based on which you want to filter the dataset.<\/li><li><strong>Operator<\/strong>:&nbsp;<em>[Optional argument]<\/em>&nbsp;In case you\u2019re using criteria 2 as well, you can combine these two criteria based on the Operator. The following operators are available for use:&nbsp;<em>xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues<\/em><\/li><li><strong>Criteria2<\/strong>:&nbsp;<em>[Optional argument]<\/em>&nbsp;This is the second criteria on which you can filter the dataset.<\/li><li><strong>VisibleDropDown<\/strong>:&nbsp;<em>[Optional argument]<\/em>&nbsp;You can specify whether you want the filter drop-down icon to appear in the filtered columns or not. This argument can be TRUE or FALSE.<\/li><\/ul>\n\n\n\n<p>Apart from Expression, all the other arguments are optional.https:\/\/b22196cd5e66bbcddc8025280cf76bdd.safeframe.googlesyndication.com\/safeframe\/1-0-38\/html\/container.html<\/p>\n\n\n\n<p>In case you don\u2019t use any argument, it would simply apply or remove the filter icons to the columns.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Sub FilterRows()\nWorksheets(\"Filter Data\").Range(\"A1\").AutoFilter\nEnd Sub<\/pre>\n\n\n\n<p>The above code would simply apply the Autofilter method to the columns (or if it\u2019s already applied, it will remove it).<\/p>\n\n\n\n<p>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.<\/p>\n\n\n\n<p>In case you have any filtered data, it will remove the filters and show you the full dataset.<\/p>\n\n\n\n<p>Now let\u2019s see some examples of using Excel VBA Autofilter that will make it\u2019s usage clear.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Example: Filtering Data based on a Text condition<\/h3>\n\n\n\n<p>Suppose you have a dataset as shown below and you want to filter it based on the \u2018Item\u2019 column.<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><img decoding=\"async\" src=\"https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/Data-set-for-VBA-Autofilter.png\" alt=\"Data set for VBA Autofilter\" class=\"wp-image-26901\"\/><\/figure><\/div>\n\n\n\n<p>The below code would filter all the rows where the item is \u2018Printer\u2019.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Sub FilterRows()\nWorksheets(\"Sheet1\").Range(\"A1\").AutoFilter Field:=2, Criteria1:=\"Printer\"\nEnd Sub<\/pre>\n\n\n\n<p>The above code refers to Sheet1 and within it, it refers to A1 (which is a cell in the dataset).<\/p>\n\n\n\n<p>Note that here we have used Field:=2, as the item column is the second column in our dataset from the left.<\/p>\n\n\n\n<p>Now if you\u2019re thinking \u2013 why do I need to do this using a VBA code. This can easily be done using inbuilt filter functionality.&nbsp;<\/p>\n\n\n\n<p>You\u2019re right!<\/p>\n\n\n\n<p>If this is all you want to do, better used the inbuilt Filter functionality.<\/p>\n\n\n\n<p>But as you read the remaining tutorial, you\u2019ll see that this can be combined with some extra code to create powerful automation.<\/p>\n\n\n\n<p>But before I show you those, let me first cover a few examples to show you what all the AutoFilter method can do.<\/p>\n\n\n\n<p><strong><a href=\"https:\/\/www.dropbox.com\/s\/bcj2r1xn1iriql8\/VBA%20Autofilter.xlsm?dl=0\" target=\"_blank\" rel=\"noreferrer noopener\">Click here<\/a><\/strong>&nbsp;to download the example file and follow along.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Example: Multiple Criteria (AND\/OR) in the Same Column<\/h3>\n\n\n\n<p>Suppose I have the same dataset, and this time I want to filter all the records where the item is either \u2018Printer\u2019 or \u2018Projector\u2019.<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><img decoding=\"async\" src=\"https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/Data-set-for-VBA-Autofilter.png\" alt=\"Data set for VBA Autofilter\" class=\"wp-image-26901\"\/><\/figure><\/div>\n\n\n\n<p>The below code would do this:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Sub FilterRowsOR()\nWorksheets(\"Sheet1\").Range(\"A1\").AutoFilter Field:=2, Criteria1:=\"Printer\", Operator:=xlOr, Criteria2:=\"Projector\"\nEnd Sub<\/pre>\n\n\n\n<p>Note that here I have used the&nbsp;<strong>xlOR<\/strong>&nbsp;operator.<\/p>\n\n\n\n<p>This tells VBA to use both the criteria and filter the data if any of the two criteria are met.<\/p>\n\n\n\n<p>Similarly, you can also use the AND criteria.<\/p>\n\n\n\n<p>For example, if you want to filter all the records where the quantity is more than 10 but less than 20, you can use the below code:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Sub FilterRowsAND()\nWorksheets(\"Sheet1\").Range(\"A1\").AutoFilter Field:=4, Criteria1:=\"&gt;10\", _\n    Operator:=xlAnd, Criteria2:=\"&lt;20\"\nEnd Sub<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Example: Multiple Criteria With Different Columns<\/h3>\n\n\n\n<p>Suppose you have the following dataset.<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><img decoding=\"async\" src=\"https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/Data-set-for-VBA-Autofilter.png\" alt=\"Data set for VBA Autofilter\" class=\"wp-image-26901\"\/><\/figure><\/div>\n\n\n\n<p>With Autofilter, you can filter multiple columns at the same time.<\/p>\n\n\n\n<p>For example, if you want to filter all the records where the item is \u2018Printer\u2019 and the Sales Rep is \u2018Mark\u2019, you can use the below code:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Sub FilterRows()\nWith Worksheets(\"Sheet1\").Range(\"A1\")\n.AutoFilter field:=2, Criteria1:=\"Printer\"\n.AutoFilter field:=3, Criteria1:=\"Mark\"\nEnd With\nEnd Sub<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Example: Filter Top 10 Records Using the AutoFilter Method<\/h3>\n\n\n\n<p>Suppose you have the below dataset.<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><img decoding=\"async\" src=\"https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/Data-set-for-VBA-Autofilter.png\" alt=\"Data set for VBA Autofilter\" class=\"wp-image-26901\"\/><\/figure><\/div>\n\n\n\n<p>Below is the code that will give you the top 10 records (based on the quantity column):<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Sub FilterRowsTop10()\nActiveSheet.Range(\"A1\").AutoFilter Field:=4, Criteria1:=\"10\", Operator:=xlTop10Items\nEnd Sub<\/pre>\n\n\n\n<p>In the above code, I have used ActiveSheet. You can use the sheet name if you want.<\/p>\n\n\n\n<p>Note that in this example, if you want to get the top 5 items, just change the number in&nbsp;<strong>Criteria1:=\u201d10\u2033<\/strong>&nbsp;from 10 to 5.<\/p>\n\n\n\n<p>So for top 5 items, the code would be:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Sub FilterRowsTop5()\nActiveSheet.Range(\"A1\").AutoFilter Field:=4, Criteria1:=\"5\", Operator:=xlTop10Items\nEnd Sub<\/pre>\n\n\n\n<p>It may look weird, but no matter how many top items you want, the Operator value always remains&nbsp;<strong>xlTop10Items.<\/strong><\/p>\n\n\n\n<p>Similarly, the below code would give you the bottom 10 items:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Sub FilterRowsBottom10()\nActiveSheet.Range(\"A1\").AutoFilter Field:=4, Criteria1:=\"10\", Operator:=xlBottom10Items\nEnd Sub<\/pre>\n\n\n\n<p>And if you want the bottom 5 items,&nbsp;change the number in&nbsp;<strong>Criteria1:=\u201d10\u2033<\/strong>&nbsp;from 10 to 5.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Example: Filter Top 10 Percent Using the AutoFilter Method<\/h3>\n\n\n\n<p>Suppose you have the same data set (as used in the previous examples).<\/p>\n\n\n\n<p>Below is the code that will give you the top 10 percent records (based on the quantity column):<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Sub FilterRowsTop10()\nActiveSheet.Range(\"A1\").AutoFilter Field:=4, Criteria1:=\"10\", Operator:=xlTop10Percent\nEnd Sub<\/pre>\n\n\n\n<p>In our dataset, since we have 20 records, it will return the top 2 records (which is 10% of the total records).<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Example: Using Wildcard Characters in Autofilter<\/h3>\n\n\n\n<p>Suppose you have a dataset as shown below:<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><img decoding=\"async\" src=\"https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/Data-set-for-Wildcard-filter.png\" alt=\"Data set for Wildcard filter\" class=\"wp-image-26907\"\/><\/figure><\/div>\n\n\n\n<p>If you want to filter all the rows where the item name contains the word \u2018Board\u2019, you can use the below code:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Sub FilterRowsWildcard()\nWorksheets(\"Sheet1\").Range(\"A1\").AutoFilter Field:=2, Criteria1:=\"*Board*\"\nEnd Sub<\/pre>\n\n\n\n<p>In the above code, I have used the&nbsp;wildcard character&nbsp;* (asterisk) before and after the word \u2018Board\u2019 (which is the criteria).<\/p>\n\n\n\n<p>An asterisk can represent any number of characters. So this would filter any item that has the word \u2018board\u2019 in it.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Example: Copy Filtered Rows into a New Sheet<\/h3>\n\n\n\n<p>If you want to not only filter the records based on criteria but also copy the filtered rows, you can use the below macro.<\/p>\n\n\n\n<p>It copies the filtered rows, adds a new worksheet,&nbsp; and then pastes these copied rows into the new sheet.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Sub CopyFilteredRows()\nDim rng As Range\nDim ws As Worksheet\nIf Worksheets(\"Sheet1\").AutoFilterMode = False Then\nMsgBox \"There are no filtered rows\"\nExit Sub\nEnd If\nSet rng = Worksheets(\"Sheet1\").AutoFilter.Range\nSet ws = Worksheets.Add\nrng.Copy Range(\"A1\")\nEnd Sub<\/pre>\n\n\n\n<p>The above code would check if there are any filtered rows in Sheet1 or not.<\/p>\n\n\n\n<p>If there are no filtered rows, it will show a message box stating that.<\/p>\n\n\n\n<p>And if there are filtered rows, it will copy those, insert a new worksheet, and paste these rows on that newly inserted worksheet.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Example: Filter Data based on a Cell Value<\/h3>\n\n\n\n<p>Using Autofilter in VBA along with a&nbsp;drop-down list, you can create a functionality where as soon as you select an item from the drop-down, all the records for that item are filtered.<\/p>\n\n\n\n<p>Something as shown below:<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><img decoding=\"async\" src=\"https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/source\/rev-029371b\/wp-content\/uploads\/2018\/08\/Autofilter-from-drop-down-selection.gif\" alt=\"Autofilter from drop down selection\" class=\"wp-image-26908\"\/><\/figure><\/div>\n\n\n\n<p><strong><a href=\"https:\/\/www.dropbox.com\/s\/bcj2r1xn1iriql8\/VBA%20Autofilter.xlsm?dl=0\" target=\"_blank\" rel=\"noreferrer noopener\">Click here<\/a><\/strong>&nbsp;to download the example file and follow along.<\/p>\n\n\n\n<p>This type of construct can be useful when you want to quickly filter data and then use it further in your work.<\/p>\n\n\n\n<p>Below is the code that will do this:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Private Sub Worksheet_Change(ByVal Target As Range)\nIf Target.Address = \"$B$2\" Then\n If Range(\"B2\") = \"All\" Then\n  Range(\"A5\").AutoFilter\n Else\n  Range(\"A5\").AutoFilter Field:=2, Criteria1:=Range(\"B2\")\n End If\nEnd If\nEnd Sub<\/pre>\n\n\n\n<p>This is a&nbsp;worksheet event code, which gets executed only when there is a change in the worksheet and the target cell is B2 (where we have the drop-down).<\/p>\n\n\n\n<p>Also, an&nbsp;If Then Else&nbsp;condition is used to check if the user has selected \u2018All\u2019 from the drop down. If All is selected, the entire data set is shown.<\/p>\n\n\n\n<p>This code is NOT placed in a module.<\/p>\n\n\n\n<p>Instead, it needs to be placed in&nbsp;the backend of the worksheet that has this data.<\/p>\n\n\n\n<p>Here are the steps to put this code in the worksheet code window:<\/p>\n\n\n\n<ol class=\"wp-block-list\"><li>Open the VB Editor (keyboard shortcut \u2013 ALT + F11).<\/li><li>In the Project Explorer pane, double-click on the Worksheet name in which you want this filtering functionality.<img fetchpriority=\"high\" decoding=\"async\" alt=\"Double Click on the Sheet Name\" width=\"342\" height=\"320\" src=\"https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/Double-Click-on-the-Sheet-Name.png\" srcset=\"https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/Double-Click-on-the-Sheet-Name.png 342w, https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/Double-Click-on-the-Sheet-Name-321x300.png 321w, https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/Double-Click-on-the-Sheet-Name-50x47.png 50w, https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/Double-Click-on-the-Sheet-Name-250x234.png 250w, https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/Double-Click-on-the-Sheet-Name-107x100.png 107w, https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/Double-Click-on-the-Sheet-Name-214x200.png 214w, https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/Double-Click-on-the-Sheet-Name-160x150.png 160w\"><\/li><li>In the worksheet code window, copy and paste the above code.<img decoding=\"async\" alt=\"Pasting the code in the Worksheet code window\" width=\"740\" height=\"434\" src=\"https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/Pasting-the-code-in-the-Worksheet-code-window.png\" srcset=\"https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/Pasting-the-code-in-the-Worksheet-code-window.png 740w, https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/Pasting-the-code-in-the-Worksheet-code-window-512x300.png 512w, https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/Pasting-the-code-in-the-Worksheet-code-window-50x29.png 50w, https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/Pasting-the-code-in-the-Worksheet-code-window-250x147.png 250w, https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/Pasting-the-code-in-the-Worksheet-code-window-171x100.png 171w, https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/Pasting-the-code-in-the-Worksheet-code-window-341x200.png 341w, https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/Pasting-the-code-in-the-Worksheet-code-window-256x150.png 256w\"><\/li><li>Close the VB Editor.<\/li><\/ol>\n\n\n\n<p>Now when you use the drop-down list, it will automatically filter the data.<\/p>\n\n\n\n<p>This is a&nbsp;worksheet event code, which gets executed only when there is a change in the worksheet and the target cell is B2 (where we have the drop-down).<\/p>\n\n\n\n<p>Also, an&nbsp;If Then Else&nbsp;condition is used to check if the user has selected \u2018All\u2019 from the drop down. If All is selected, the entire data set is shown.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Turn Excel AutoFilter ON\/OFF using VBA<\/h2>\n\n\n\n<p>When applying Autofilter to a range of cells, there may already be some filters in place.<\/p>\n\n\n\n<p>You can use the below code turn off any pre-applied auto filters:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Sub TurnOFFAutoFilter()\n  Worksheets(\"Sheet1\").AutoFilterMode = False\nEnd Sub<\/pre>\n\n\n\n<p>This code checks the entire sheets and removes any filters that have been applied.<\/p>\n\n\n\n<p>If you don\u2019t want to turn off filters from the entire sheet but only from a specific dataset, use the below code:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Sub TurnOFFAutoFilter()\n  If Worksheets(\"Sheet1\").Range(\"A1\").AutoFilter Then\n    Worksheets(\"Sheet1\").Range(\"A1\").AutoFilter\n  End If\nEnd Sub<\/pre>\n\n\n\n<p>The above code checks whether there are already filters in place or not.<\/p>\n\n\n\n<p>If filters are already applied, it removes it, else it does nothing.<\/p>\n\n\n\n<p>Similarly, if you want to turn on AutoFilter, use the below code:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Sub TurnOnAutoFilter()\n  If Not Worksheets(\"Sheet1\").Range(\"A4\").AutoFilter Then\n    Worksheets(\"Sheet1\").Range(\"A4\").AutoFilter\n  End If\nEnd Sub<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Check if AutoFilter is Already Applied<\/h2>\n\n\n\n<p>If you have a sheet with multiple datasets and you want to make sure you know that there are no filters already in place, you can use the below code.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Sub CheckforFilters()\nIf ActiveSheet.AutoFilterMode = True Then\nMsgBox \"There are Filters already in place\"\nElse\nMsgBox \"There are no filters\"\nEnd If\nEnd Sub<\/pre>\n\n\n\n<p>This code uses a&nbsp;message box&nbsp;function that displays a message \u2018There are Filters already in place\u2019 when it finds filters on the sheet, else it shows \u2018There are no filters\u2019.<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><img decoding=\"async\" src=\"https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/source\/rev-029371b\/wp-content\/uploads\/2018\/08\/Filter-Message-Box.png\" alt=\"Filter Message Box\" class=\"wp-image-26911\"\/><\/figure><\/div>\n\n\n\n<h2 class=\"wp-block-heading\">Show All Data<\/h2>\n\n\n\n<p>If you have filters applied to the dataset and you want to show all the data, use the below code:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Sub ShowAllData()\nIf ActiveSheet.FilterMode Then ActiveSheet.ShowAllData\nEnd Sub<\/pre>\n\n\n\n<p>The above code checks whether the FilterMode is TRUE or FALSE.<\/p>\n\n\n\n<p>If it\u2019s true, it means a filter has been applied and it uses the ShowAllData method to show all the data.<\/p>\n\n\n\n<p>Note that this does not remove the filters. The filter icons are still available to be used.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Using AutoFilter on Protected Sheets<\/h2>\n\n\n\n<p>By default, when you&nbsp;protect a sheet, the filters won\u2019t work.<\/p>\n\n\n\n<p>In case you already have filters in place, you can enable AutoFilter to make sure it works even on protected sheets.<\/p>\n\n\n\n<p>To do this, check the Use Autofilter option while protecting the sheet.<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><img decoding=\"async\" src=\"https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/Check-the-Use-Autofilter-Option.png\" alt=\"Check the Use Autofilter Option\" class=\"wp-image-26913\"\/><\/figure><\/div>\n\n\n\n<p>While this works when you already have filters in place, in case you try to add Autofilters using a VBA code, it won\u2019t work.<\/p>\n\n\n\n<p>Since the sheet is protected, it wouldn\u2019t allow any macro to run and make changes to the Autofilter.<\/p>\n\n\n\n<p>So you need to use a code to protect the worksheet and make sure auto filters are enabled in it.<\/p>\n\n\n\n<p>This can be useful when you have created a dynamic filter (something I covered in the example \u2013&nbsp; \u2018Filter Data based on a Cell Value\u2019).<\/p>\n\n\n\n<p>Below is the code that will protect the sheet, but at the same time, allow you to use Filters as well as VBA macros in it.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Private Sub Workbook_Open()\nWith Worksheets(\"Sheet1\")\n.EnableAutoFilter = True\n.Protect Password:=\"password\", Contents:=True, UserInterfaceOnly:=True\nEnd With\nEnd Sub<\/pre>\n\n\n\n<p>This code needs to be placed in ThisWorkbook code window.<\/p>\n\n\n\n<p>Here are the steps to put the code in ThisWorkbook code window:<\/p>\n\n\n\n<ol class=\"wp-block-list\"><li>Open the VB Editor (keyboard shortcut \u2013 ALT + F11).<\/li><li>In the Project Explorer pane, double-click on the ThisWorkbook object.<img decoding=\"async\" alt=\"ThisWorkbook Object in Project Explorer\" width=\"408\" height=\"304\" src=\"https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/ThisWorkbook-Object-in-Project-Explorer.png\" srcset=\"https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/ThisWorkbook-Object-in-Project-Explorer.png 408w, https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/ThisWorkbook-Object-in-Project-Explorer-403x300.png 403w, https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/ThisWorkbook-Object-in-Project-Explorer-50x37.png 50w, https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/ThisWorkbook-Object-in-Project-Explorer-250x186.png 250w, https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/ThisWorkbook-Object-in-Project-Explorer-134x100.png 134w, https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/ThisWorkbook-Object-in-Project-Explorer-268x200.png 268w, https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/ThisWorkbook-Object-in-Project-Explorer-201x150.png 201w\"><\/li><li>In the code window that opens, copy and paste the above code.<img loading=\"lazy\" decoding=\"async\" alt=\"VBA code in ThisWorkbook\" width=\"740\" height=\"366\" src=\"https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/VBA-code-in-ThisWorkbook.png\" srcset=\"https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/VBA-code-in-ThisWorkbook.png 740w, https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/VBA-code-in-ThisWorkbook-600x297.png 600w, https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/VBA-code-in-ThisWorkbook-50x25.png 50w, https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/VBA-code-in-ThisWorkbook-250x124.png 250w, https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/VBA-code-in-ThisWorkbook-202x100.png 202w, https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/VBA-code-in-ThisWorkbook-400x198.png 400w, https:\/\/v1.nitrocdn.com\/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe\/assets\/static\/optimized\/rev-029371b\/wp-content\/uploads\/2018\/08\/VBA-code-in-ThisWorkbook-303x150.png 303w\"><\/li><\/ol>\n\n\n\n<p>As soon as you open the workbook and enable macros, it will&nbsp;run the macro&nbsp;automatically and protect Sheet1.<\/p>\n\n\n\n<p>However, before doing that, it will specify \u2018EnableAutoFilter = True\u2019, which means that the filters would work in the protected sheet as well.<\/p>\n\n\n\n<p>Also, it sets the \u2018UserInterfaceOnly\u2019 argument to \u2018True\u2019. This means that while the worksheet is protected, the VBA macros code would continue to work.<\/p>\n\n\n\n<p>Ref: https:\/\/trumpexcel.com\/vba-autofilter\/<\/p>\n","protected":false},"excerpt":{"rendered":"<p>A lot of Excel functionalities are also available to be used in VBA \u2013 and the&nbsp;Autofilter&nbsp;method&nbsp;is one such functionality. 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. And if you want a more advanced version of <a class=\"read-more\" href=\"https:\/\/SUMMALAI.COM\/?p=3256\">Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,18],"tags":[692,693],"class_list":["post-3256","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-microsoft-office","tag-excel-vba-autofilter","tag-vba-autofilter"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3256","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3256"}],"version-history":[{"count":2,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3256\/revisions"}],"predecessor-version":[{"id":3282,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3256\/revisions\/3282"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3256"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3256"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3256"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}