The Ultimate Guide to Excel Filters with VBA Macros – AutoFilter Method

Bottom line: Learn how to create macros that apply filters to ranges and Tables with the AutoFilter method in VBA.  The post contains links to examples for filtering different data types including text, numbers, dates, colors, and icons.

Skill level: Intermediate

Automate Filters with VBA Macros - AutoFilter Guide

Download the File

The Excel file that contains the code can be downloaded below.  This file contains code for filtering different data types and filter types.

Writing Macros for Filters in Excel

Filters are a great tool for analyzing data in Excel.  For most analysts and frequent Excel users, filters are a part of our daily lives.  We use the filter drop-down menus to apply filters to individual columns in a data set.  This helps us tie out numbers with reports and do investigative work on our data.

How Often Do You Apply Excel Filters

Filtering can also be a time consuming process.  Especially when we are applying filters to multiple columns on large worksheets, or filtering data to then copy/paste it to other worksheets or workbooks.

This article explains how to create macros to automate the filtering process.  This is an extensive guide on the AutoFilter method in VBA.

I also have articles with examples for different filters and data types including: blanks, text, numbers, dates, colors & icons, and clearing filters.

The Macro Recorder is Your Friend (& Enemy)

We can easily get the VBA code for filters by turning on the macro recorder, then applying one or more filters to a range/Table.

Here are the steps to create a filter macro with the macro recorder:

  1. Turn the macro recorder on:
    1. Developer tab > Record Macro.
    2. Give the macro a name, choose where you want the code saved, and press OK.
  2. Apply one or more filters using the filter drop-down menus.
  3. Stop the recorder.
  4. Open the VB Editor (Developer tab > Visual Basic) to view the code.

If you've already used the macro recorder for this process, then you know how useful it can be.  Especially as our filter criteria gets more complex.

The code will look something like the following.

Sub Filters_Macro_Recorder()
' Filters_Macro_Recorder Macro

    ActiveSheet.ListObjects("tblData").Range.AutoFilter Field:=4, Criteria1:= _
        "Product 2"
    ActiveSheet.ListObjects("tblData").Range.AutoFilter Field:=4
    ActiveSheet.ListObjects("tblData").Range.AutoFilter Field:=5, Criteria1:= _
        ">=500", Operator:=xlAnd, Criteria2:="<=1000"
End Sub

We can see that each line uses the AutoFilter method to apply the filter to the column.  It also contains information about the criteria for the filter.

This is where it can get complex, confusing, and frustrating.  It can be difficult to understand what the code means when trying to modify it for a different data set or scenario.  So let's take a look at how the AutoFilter method works.

The AutoFilter Method Explained

The AutoFilter method is used to clear and apply filters to a single column in a range or Table in VBA.  It automates the process of applying filters through the filter drop-down menus, and does all that work for us. ๐Ÿ™‚

VBA AutoFilter Automates Filter Drop-down Menus

It can be used to apply filters to multiple columns by writing multiple lines of code, one for each column.  We can also use AutoFilter to apply multiple filter criteria to a single column, just like you would in the filter drop-down menu by selecting multiple check boxes or specifying a date range.

Writing AutoFilter Code

Here are step-by-step instructions for writing a line of code for AutoFilter

Step 1 : Referencing the Range or Table

The AutoFilter method is a member of the Range object.  So we must reference a range or Table that the filters are applied to on the sheet.  This will be the entire range that the filters are applied to.

AutoFilter Method is Member of Range Object

The following examples will enable/disable filters on range B3:G1000 on the AutoFilter Guide sheet.

Sub AutoFilter_Range()
'AutoFilter is a member of the Range object
  'Reference the entire range that the filters are applied to
  'AutoFilter turns filters on/off when no parameters are specified.
  'Fully qualified reference starting at Workbook level
  ThisWorkbook.Worksheets("AutoFilter Guide").Range("B3:G1000").AutoFilter

End Sub

Here is an example using Excel Tables.

Sub AutoFilter_Table()
'AutoFilters on Tables work the same way.

Dim lo As ListObject 'Excel Table

  'Set the ListObject (Table) variable
  Set lo = Sheet1.ListObjects(1)
  'AutoFilter is member of Range object
  'The parent of the Range object is the List Object
End Sub

The AutoFilter method has 5 optional parameters, which we'll look at next.  If we don't specify any of the parameters, like the examples above, then the AutoFilter method will turn the filters on/off for the referenced range.  It is toggle.  If the filters are on they will be turned off, and vice-versa.

Ranges or Tables?

Filters work the same on both regular ranges and Excel Tables.

AutoFilter on Regular Range or Excel Table

My preferred method is to use Tables because we don't have to worry about changing range references as the table grows or shrinks.  However, the code will be the same for both objects.  The rest of the code examples use Excel tables, but you can easily modify this for regular ranges.

The 5 (or 6) AutoFilter Paramaters

The AutoFilter method has 5 (or 6) optional parameters that are used to specify the filter criteria for a column.  Here is a list of the parameters.

AutoFilter Parameters Optional Screentip in VB Editor VBA
FieldOptionalThe number of the column within the filter range that the filter will be applied to. This is the column number within the filter range, NOT the column number of the worksheet.
Criteria1OptionalA string wrapped in quotation marks that is used to specify the filter criteria.  Comparison operators can be included for less than or greater than filters.  Many rules apply depending on the data type of the column.  See examples below.
OperatorOptionalSpecifies the type of filter for different data types and criteria by using one of the XlAutoFilterOperator constants.  See this MSDN help page for a detailed list, and list in macro examples below.
Criteria2OptionalUsed in combination with the Operator parameter and Criteria1 to create filters for multiple criteria or ranges.  Also used for specific date filters for multiple items.
VisibleDropDownOptionalDisplays or hides the filter drop-down button for an individual column (field).
SubfieldOptionalNot sure yet…

We can use a combination of these parameters to apply various filter criteria for different data types.  The first four are the most important, so let's take a look at how to apply those.

Step 2: The Field Parameter

The first parameter is the Field.  For the Field parameter we specify a number that is the column number that the filter will be applied to.  This is the column number within the filter range that is the parent of the AutoFilter method.  It is NOT number of the column on the worksheet.

In the example below Field 4 is the Product column because it is the 4th column in the filter range/Table.

Field Parameter Value is Column Number of the Range or Table

The column filter is cleared when we only specify the the Field parameter, and no other criteria.

Field Parameter Only Clears Single Column Filter

We can also use a variable for the Field parameter and set it dynamically.  I explain that in more detail below.

Step 3: The Criteria Parameters

There are two parameters that can be used to specify the filter Criteria, Criteria1 and Criteria2.  We use a combination of these parameters and the Operator parameter for different types of filters.  This is where things get tricky, so let's start with a simple example.

'Filter the Product column for a single item
lo.Range.AutoFilter Field:=4, Criteria1:="Product 2"

This would be the same as selecting a single item from the checkbox list in the filter drop-down menu.

VBA AutoFilter Code to Filter for Single Item in Filter Drop-down Menu

General Rules for Criteria1 and Criteria2

The values we specify for Criteria1 and Criteria2 can get tricky.  Here are some general guidelines for how to reference the Criteria parameter values.

  • The criteria value is a string wrapped in quotation marks.  There are a few exceptions where the criteria is a constant for date time period and above/below average.
  • When specifying filters for single numbers or dates, the number formatting must match the number formatting that is applied in the range/table.
  • The comparison operator for greater/less than is also included inside the quotation marks, before the number.
  • Quotation marks are also used for filters for  blanks “=” and non-blanks “<>”.
General Rules for Criteria Parameters
'Filter for date greater than or equal to Jan 1 2015
lo.Range.AutoFilter Field:=1, Criteria1:=">=1/1/2015"

' The comparison operator >= is inside the quotation marks
' for the Criteria1 parameter.

' The date formatting in the code matches the formatting
' applied to the cells in the worksheet.

Step 4: The Operator Parameter

What if we want to select multiple items from the filter drop-down?  Or do a filter for a range of dates or numbers?

For this we need the Operator.  The Operator parameter is used to specify what type of filter we want to apply.  This can vary based on the type of data in the column.  One of the following 11 constants must be used for the Operator.

xlAnd1Include both Criteria1 and Criteria2.  Can be used for date or number ranges.
xlBottom10Items4Lowest-valued items displayed (number of items specified in Criteria1).
xlBottom10Percent6Lowest-valued items displayed (percentage specified in Criteria1).
xlFilterCellColor8Fill Color of the cell
xlFilterDynamic11Dynamic filter used for Above/Below Average and Date Periods
xlFilterFontColor9Color of the font in the cell
xlFilterIcon10Filter icon created by conditional formatting
xlFilterValues7Used for filters with multiple criteria specified with an Array function.
xlOr2Include either Criteria1 or Criteria2. Can be used for date and number ranges.
xlTop10Items3Highest-valued items displayed (number of items specified in Criteria1).
xlTop10Percent5Highest-valued items displayed (percentage specified in Criteria1).

Here is a link to the MSDN help page that contains the list of constants for XlAutoFilterOperator Enumeration.

The operator is used in combination with Criteria1 and/or Criteria2, depending on the data type and filter type.  Here are a few examples.

'Filter for list of multiple items, Operator is xlFilterValues
lo.Range.AutoFilter _
          Field:=iCol, _
          Criteria1:=Array("Product 4", "Product 5", "Product 6"), _
'Filter for Date Range (between dates), Operator is xlAnd
lo.Range.AutoFilter _
          Field:=iCol, _
          Criteria1:=">=1/1/2014", _
          Operator:=xlAnd, _

So that is the basics of writing a line of code for the AutoFilter method.  It gets more complex with different data types.  So I've provided many examples below that contain most of the combinations of Criteria and Operator for different types of filters.

AutoFilter is NOT Additive

When an AutoFilter line of code is run, it first clears any filters applied to that column (Field), then applies the filter criteria that is specified in the line of code.

This means it is NOT additive.  The following 2 lines will NOT create a filter for Product 1 and Product 2.  After the macro is run, the Product column will only be filtered for Product 2.

'AutoFilter is NOT addititive.  It first any filters applied
'in the column before applying the new filter
lo.Range.AutoFilter Field:=4, Criteria1:="Product 3"
'This line of code will filter the column for Product 2 only
'The filter for Product 3 above will be cleared when this line runs.
lo.Range.AutoFilter Field:=4, Criteria1:="Product 2"

If you want to apply a filter with multiple criteria to a single column, then you can specify that with the Criteria and Operator parameters.

How to Set the Field Number Dynamically

If we add/delete/move columns in the filter range, then the field number for a filtered column might change.  Therefore, I try to avoid hard-coding a number for the Field parameter whenever possible.

We can use a variable instead and use some code to find the column number by it's name.  Here are two examples for regular ranges and Tables.

Use Variable for Filter Field Column Number in VBA with Index Property
Sub Dynamic_Field_Number()
'Techniques to find and set the Field based on the column name.
Dim lo As ListObject
Dim iCol As Long
  'Set reference to the first Table on the sheet
  Set lo = Sheet1.ListObjects(1)
  'Set filter field
  iCol = lo.ListColumns("Product").Index
  'Use Match function for regular ranges
  'iCol = WorksheetFunction.Match("Product", Sheet1.Range("B3:G3"), 0)

  'Use the variable for the Field parameter value
  lo.Range.AutoFilter Field:=iCol, Criteria1:="Product 3"

End Sub

The column number will be found every time we run the macro.  We don't have to worry about changing the field number when the column moves.  This saves time and prevents errors (win-win)! ๐Ÿ™‚

Use Excel Tables with Filters

There are a lot of advantages to using Excel Tables, especially with the AutoFilter method.  Here are a few of the major reasons I prefer Tables.

  • We don't have to redefine the range in VBA as the data range changes size (rows/columns are added/deleted).  The entire Table is referenced with the ListObject object.
  • It's easy to reference the data in the Table after filters are applied.  We can use the DataBodyRange property to reference visible rows to copy/paste, format, modify values, etc.
  • We can have multiple Tables on the same sheet, and therefore multiple filter ranges.  With regular ranges we can only have one filtered range per sheet.
  • The code to clear all filters on a Table is easier to write.

Filters & Data Types

The filter drop-down menu options change based on what type of data is in the column.   We have different filters for text, numbers, dates, and colors.  This creates A LOT of different combinations of Operators and Criteria for each type of filter.

I created separate posts for each of these filter types.  The posts contain explanations and VBA code examples.

The file in the downloads section above contains all of these code samples in one place.  You can add it to your Personal Macro Workbook and use the macros in your projects.

Why is the AutoFilter Method so Complex?

This post was inspired by a question from Chris, a member of The VBA Pro Course.  The combinations of Criteria and Operators can be confusing and complex.  Why is this?

Well, filters have evolved over the years.  We saw a lot of new filter types introduced in Excel 2010, and the feature is continuing to be improved.  However, the parameters of the AutoFilter method haven't changed.  This is great for compatibility with older versions, but also means the new filter types are being worked into the existing parameters.

Most of the filter code makes sense, but can be tricky to figure out at first.  Fortunately we have the macro recorder to help with that.

I hope you can use this post and Excel file as a guide to writing macros for filters.  Automating filters can save us and our users a ton of time, especially when using these techniques in a larger data automation project.

Please leave a comment below with any questions or suggestions.  Thank you! ๐Ÿ™‚


Your email address will not be published. Required fields are marked *

  • Clear explanation, why did I not think of matching for column header. Excellent idea.

    Does this work for Pivot Tables?

    • Thank you John! The VBA filters for pivot tables are different. They use the PivotFilters.Add method for the filters in the Rows & Columns areas. Some of the techniques and rules for the AutoFilter method apply to PivotFilters.Add. So this training on AutoFilter might make it easier to understand PivotFilters.Add. We actually use the Add2 method in modern versions of Excel. I’ll add that topic to the list for future posts.

      Thanks again! ๐Ÿ™‚

    • Thank you Tom! These techniques will not work with pivot tables. Filters in the Rows & Columns areas of a pivot table use the PivotFilters.Add method. There are some similarities to AutoFilter, so this training should help get you started. I’ll add pivot table filters to the list for future posts. Thanks again! ๐Ÿ™‚

  • Dear
    Is it possible to generate a video clips on “Guide to Excel Filters with VBA Macros โ€“ AutoFilter Method”.

    Thanks in advance

  • Absolutely outstanding references Jon…!!! Thank you very much for so thoroughly covering this confusing topic…:-)

    • Thank you Bob! I really appreciate the nice feedback. I felt like I wrote a book on AutoFilters, and probably still have more to cover.

      They are not necessarily straightforward, but a very useful tool when automating all kinds of data related tasks. More on that in the future as well.

      Thanks again! ๐Ÿ™‚

  • Absolutely great thorough explanations and references as usual Jon!

    It was excellent the code for dynamically reference the Field Number in VBA auto filter and using the Tables objects in the code.

    Thanks Jon!

  • By far, your examples are the most easiest to understand and are clearly and thoughtfully written. I have many subscriptions to MVP and I am at home with yours the most. Thank you for sharing your knowledge and how better to spend our time. Continuous process improvement is my motto, so your input is highly valued.

  • Really Useful information for me, and request you to share same step by step information about advance filter also

  • Thank you for the article, Jon
    I had an experience with some universal macro command related to autofilter. An idea was following
    Right click to the Pivot Table row/column labels returns two convenient cases:
    1) Keep only selected items
    2) Hide selected items
    Though right click to normal Excel table only one (analogy of the item 1 above):
    1) Filter by selected cell’s value
    Why we cannot automate both items (1 and 2) for normal Excel table by using VBA? Why can’t we use the same macro code for filtering of Pivot Table’s labels as well?
    I created two macros that almost do it:
    1. One macro is called “Filter_Equal” (it allows select contiguous/noncontiguous one cell/multiple cell ranges of normal Excel table and filter them. It also allows selecting only contiguous one cell/multiple cell ranges of Pivot Table labels and filter them
    2. Another macro is called “Filter_Not_Equal” and it makes the same things as the previous one but hides the selection
    The problems happen (sometimes) for some users when they try to hide selected dates (we use format dd/mm/yyyy and sometimes VBA does not cope with it) and True/False values if they are non boolean, but text. If you know how to filter them please describe
    Anyway we have been using both macros several years and they help filtering tables/pivot tables
    I can share the VBA code if you are interested

    • Hi Ivan,

      Great suggestion. My PivotPal Add-in has a similar feature that allows you to filter the source data based on the selected cell in the pivot table.

      Unfortunately VBA does not allow us to read the date filters for grouped date fields. This makes it very challenging, if not impossible, to build macros that filter for date fields. When I was developing PivotPal I tried to come up with some workarounds, but nothing was ever 100% bullet proof. There are a lot of variables and factors at play. Let me know if you find a solution.

      Thanks again!

      • Hi, Jon
        For filtering of Pivot Table I simply apply VBA Send Keys:
        1) Filter ‘Keep only selected items’
        SendKeys “+{F10}tk~”
        2) Filter ‘Hide selected items’
        SendKeys “+{F10}th~”
        It works fine for filtering of normal labels and for grouped ones as well
        I have sent full VBA procedure to you by mail

  • Thanks for this very useful information. I’ve been looking for this kind of overview for ages. Thanks again. Arnold

  • Hi,

    Thank you so much for your help. This is my problem.

    I try to use the information contained in one column to fill other columns. For example in col A, there are Product p1 and product p2.
    I would like to fill col B as follow:
    I filter Product p1 in col A, and write John in the first data celle of column B I copy downwards John in col B, so that at each time we have p1 in col A, we will have John in col B
    Then I filter Product p2, and write Paul in the first data cell of column B I copy downards Paul in col B, so that at each time we have p2 in col A, we will have Paul in col B

    col A Col B
    p1 John
    p2 Paul

    If i do it manually I have no problem. Now I try to record a macro doing the same thing and it does not work.
    This is frustrating because I have to to the same work over and over again and it is time consuming
    Is there a way to do that automatically?.
    I tried with the IF function, and it works, theoretically, but for the problem I have to solve it is not practical because I have more or less 6 columns to fill (not only col B) and approximately 10 products, not only p1 and p2.
    Can you help me find a solution?

    • Hi Oliver,
      one simple solution should be a lookup table where you put in your 10 products and the names. Then you would just need to copy in one VLOOKUP-formula per column. But maybe you cannot work this because you would need 6 lookup-tables?
      The other thing would be a combination of the match() and choose() function:


      Here no extra tables are needed, just a different formula per column.


  • Dear Jon
    May we request Ivan M. (comment dt July 25, 2018 above & comment previous to that) to share his macros. These seem to be useful.

  • does any know how to sort/filter data by x,y coordinates?
    here is the situation:

    i am pulling map data by zip code and putting it into a csv file. enclosed inside these zip codes are
    distribution areas. i have 4 lat/lons (x1,y1), (x2,y2), (x3,y3), and (x4,y4) these create a square geographic boundary that is a DA. what i would like to do is filter out the data that is not enclosed in the boundary.

    I can do this in matlab however, my company does not own matlab so i am attempting to do this in excel. please help if you can

  • does any know how to sort/filter data by x,y coordinates?
    here is the situation:

    i am pulling map data by zip code and putting it into a csv file. enclosed inside these zip codes are
    distribution areas. i have 4 lat/lons (x1,y1), (x2,y2), (x3,y3), and (x4,y4) these create a square geographic boundary that is a DA. what i would like to do is filter out the data that is not enclosed in the boundary.

    I can do this in matlab however, my company does not own matlab so i am attempting to do this in excel. please help if you can

  • Hi,
    One clarification you may want to add to the section ‘The AutoFilter Method Explained’ or the section ‘AutoFilter is NOT Additive’ is that while the AutoFilter method is not additive for a single column you can filter on multiple columns using successive calls to AutoFilter:

    Set lo = ActiveSheet.ListObjects(msTbl)

    With lo
    .Range.AutoFilter Field:=.ListColumns(“Image”).Index, Criteria1:=”*.txt”
    .Range.AutoFilter Field:=.ListColumns(“DepositDate”).Index, Criteria1:=”>=” & DepositDate
    End With

  • Hello! What if I want my range to be a variable expression in the VBA code? In my scenario, I want Excel to filter a table between a set of dates on a particular column (so I would inevitably need to use the >= and =DateMin”, Operator:=xlAnd, Criteria2:=”<=DateMax"

    • Hi Jon-Michael,

      Great question!

      We can use the WorksheetFunction.Min method to find the minimum value in a range. The WorksheetFunction object allows us to use Excel functions in VBA. So this is the same as the MIN function in Excel. There is also a Max method/function.

      The code will look like the following for an Excel Table (ListObject).

      sDate = Format(WorksheetFunction.Min(lo.ListColumns("Date").DataBodyRange), "m/d/yyyy")

      You can also use a regular range reference in the Min method’s parameter.

      sDate = Format(WorksheetFunction.Min(Sheet1.Range("Table1[Date]"), "m/d/yyyy")


      sDate = Format(WorksheetFunction.Min(Sheet1.Range("A2:A1000"), "m/d/yyyy")

      A few things to note here.

      The sDate variable is declared as a String.

      Dim sDate as String

      The Format function will convert the date value (numeric value) found by Min, to a string that is formatted with “m/d/yyyy”. This date format must match the date format of your column because AutoFilter actually does string comparisons.

      You can then use the variable in the Criteria1 or 2 parameters

      .AutoFilter Field:=iCol, Criteria1:=">" & sDate

      Checkout my article on How to Filter Dates with VBA for more info on this behavior.

      I hope that helps.

    • I should mention that you can combine all that code into the parameter value, and not use the variable. However, it will be much easier to debug the code with the variable. And you can also re-use the variable for a message box or in other places in the code.

      The Format function in VBA is similar to the TEXT function in Excel. You could also break that into two different lines to return the Min numeric value to a Long variable, then convert it to text with the Format function and set it to the string variable. I combined these two actions into one line of code, but if you are getting errors I would check each component.

      lDateVal = WorksheetFunction.Min(Sheet1.Range("Table1[Date]")
      sDate = Format(lDateVal, "m/d/yyyy")
  • Hi Jon,
    My question involves filtering a single criteria(check mark) in a column that has a changing range each day. For example, I am looking to filter for a single column’s criteria that had 16,000 rows yesterday, but now has 17,500 rows today. I want to make sure that my macro includes all rows of data.

    What would be the best code to fix do this?

    • Hi Dominic,

      The easiest way is to store your data in an Excel table. You can then reference the Table (ListObject) in the code and the filter will ALWAYS include all the rows in the table. There are examples on this page and in the sample workbook on how to use Tables with AutoFilter.

      If you don’t want to use a Table then you can also use code to find the last used cell on the sheet/range. Here is a video series that explains ways to find the last used cell with VBA.

      You might also be able to use the UsedRange or CurrentRegion properties, depending on what other data is on the sheet. I hope that helps.

  • I have a complex enquiry, I wonder if anyone has advice. I have a rota made up of several data validation lists, depending on their skill level and the tasks. I need the user to select the person for the task and then in the cell next to the selection, the number of the last training module they did comes up as a hyperlink.
    The hyperlink takes them to a training history page where all that personโ€™s training is listed. (Advanced filter?)
    From this training history, they can add the next training module which will appear at the bottom of their history and then the user can go back to the rota. The hyperlink has now changed to display the new training module number.
    The user can then move to the next person which will be a different list as it is a different skill but needs to go to the same training history page which should now display the next persons history. The hyperlink for the previous person should not be affected by the selection of the next person.
    I donโ€™t know how to go about this. Any ideas? Advanced filter based on the active cell selection? How to stop the hyperlinks changing on selection of the next person?
    Many thanks.

  • Thanks.
    No expert here but after trying to get this to work on another sheet I figured it out.
    Why would you use
    Set lo = Sheet1.ListObjects(1)
    instead of
    Set lo = ActiveSheet.ListObjects(1)
    When giving an example to newbs like me when explaining how to have things dynamic?

      • For table or range substitue name for 1
        Set lo = Sheet1.ListObjects(“Range_Name”)
        Also, sheet1 is the code name of the sheet and more predictable results when you’re working with more than one sheet

  • I’d like to be able to select a cell and use that value to filter another column.

    But all examples of autofilter I find only use literal stings or a cell range, and I cannot predict the value or its location.

    Is there a way to set criteria1 to be the value of a the cell I click on (criteria1=$val) ? Or maybe assign that value to a variable and use the variable name in the autofilter method? Non of my experiments with this have worked.

    Thank you in advance!!


    • Hi Rob,
      I’ve been working on the same issue. By scrolling down, I saw that Jon offered to use
      Criteria1:=”>” & sDate
      (after he declared some stuff).
      I wanted the user to click a button and have a prompt for inputting the filter type. I called the input type myValue.
      So my code was Criteria1:=”=” &myValue
      and it worked!
      Hope this helps. Good luck

  • I need to ensure before running some other VBA code, that certain filters in a filtered table are selected. If they aren’t selected, I need to select them, as well as any current filters. Any suggestions appreciated…

    • I am thinking that I need an array to capture the currently selected filters, then a test that the array of required filters is included in the current selection.

  • Jon, I am experiencing some difficulties in getting my pivot fields changed. This pivot is based on a table and I have added the pivot to the data model because I need the distinct count option and the filter change appears as follows when recorded:

    ActiveSheet.PivotTables(“PivotTable1”).PivotFields( _
    “[TableName].[ColumnName].[ColumnName]”).VisibleItemsList = Array(“_

    I need to make this FilterOption part variable depending on a cell, how could I do that?

    Thank you!

  • Hello, Jon! I’d like to write a macro which creates a filter using user input in cells with a set of choices from a data validation list. Lets say I have two columns of data — name and age. I’d like to have a separate cell for each variable with a List Data Validation format, and have the user choose how to filter the data via the data validation types — such as “>=10” for Age. Then, have a macro which reads the cell and builds the filter in VBA, such that when the cell is changed, the VBA code is changed on execution as well. Is this possible? I know how to build the names list in Name Manager, and find the List when setting up Data Validation. My challenge is “variablizing” the Macro filter command. Thanks!

    • Hello Roy, I’m looking for a Same Solution as Yours. Did you find how to achieve the Request of Yours? If You share it’d be of greater use for me.

  • Hi there,
    this is my small code for filtering rows in a sheet with specific row color, this is working excellent till the moment that i will make my workbook shared, then i get error 1004 with comment “autofilter method of range class failed”, use of record macro or wright the code the resault is the same, working perfect till i make the workbook shared.

    Sub color()

    ‘ color Macro

    ActiveSheet.Range(“$A$4:$O$4”).AutoFilter Field:=1, Criteria1:=RGB(33, 89 _
    , 103), Operator:=xlFilterCellColor
    ActiveWindow.SmallScroll Down:=-42

    End Sub

  • Hi guys,

    Trying to run a VBA that opens a file, filters specific columns, copies the data into a new spreadsheet and then saves the new spreadsheet as a new file.

    The criteria for what information to filter on is a supplier’s name I type into a particular cell. My master file that has the macro stored has a cell which I input the name of the supplier. I hit my magic button to start the Macro and it should then run through the process. It works with the exception that it will not filter my supplier column with the name of the supplier I type into the Master file. An example of the code I’ve been experimenting with:

    ActiveSheet.Range(“$A$1:$Z$30000″).AutoFilter Field:=2, Criteria1:=Range(” ‘[C:\Users\xxxxx\xxxxx\xxxxx\xxxxx\xxxxx\xxxxx.xlsm]Sheet1’!B3″).Value

    What’s the correct string I should be using for Criteria1 to make it use the name of the supplier in Cell B3 as the value to filter on

    • I would do it like this (fyi, untested code – may need minor adjustments for use case):

      Option Explicit

      Dim wb1 As Workbook, wb2 As Workbook
      Dim sht1 As Worksheet, sht2 As Worksheet
      Dim PathToFile As String, SheetName1 as String, SheetName 2 As String
      Dim lRow As Long
      Dim varResult As Variant

      ‘ This is the path of the file you are opening
      PathToFile = “Path\to\file\goes\here”

      ‘ Opens the file with the data to be filtered (wb2), as ReadOnly to be safe
      Set wb1 = ThisWorkbook
      Set wb2 = Workbooks.Open(PathToFile,ReadOnly:=True)

      ‘ The name of the sheets in the two workbooks you are considering in two files
      SheetName1 = “Sheet1”
      SheetName2 = “Sheet1”

      Set sht1 = wb1.Sheets(SheetName1)
      Set sht2 = wb2.Sheets(SheetName2)

      CriteriaForFilter = wb1.sht1.Range(“B3”)

      With wb2.sht2
      lRow = .Cells(Rows.Count, “Z”).End(xlUp).Row
      Set Rng = .Range(“A1:Z” & lRow)
      .Rng.AutoFilter Field:=2, Criteria1:=CriteriaForFilter
      End With

      varResult = Application.GetSaveAsFilename(FileFilter:= _
      “Excel Files (*.xlsx), *.xlsx”, Title:=”Save result”)

      If varResult False Then
      ActiveWorkbook.SaveAs Filename:=varResult
      End If

      wb2.Close SaveChanges:=False

  • Hardly “the ultimate guide”… this leaves many unanswered questions. For example, how can I detect when the user had applied a filter?

    • Range(“Table1”).ListObject.AutoFilter.FilterMode
      returns True if filter applied, False if no filter applied and errors out (error 91) when filtering is turned off.

  • For those of you looking to evaluate the current day anytime this runs, for example any day in the past, use:

    Criteria#:=”<" & Date

  • I need the vba code which will be applied to all the .xlsx files in a given folder and perform the task. The task is to search a column header “ABC” and apply filter to select only value “XYZ”, delete the hidden values, removes the filter and save the file.

    Can someone please help me with this.

  • Hi,

    I have this query related to AutoFilter. In my excel worksheet I want to apply auto filter in column A and B. Column B has only two criterias ” Queso Cabrales” and “Scottish Longbreads” and column A would have Order ID like 10248,10249,10250 etc…. First, I need to select “Queso Cabrales ” in column B and navigate to Column A and autofilter select criteria “10248”. Now i want to copy the filtered data to another sheet starting from range B3. I need to iterate through each filter criteria available in Column A and copy paste the data to another sheet below the last filled data of each criteria.

    once it is done. I need to do the same process as above for “Scottish Longbreads” filter.

  • In my excel worksheet I want to apply auto filter in column A and B. Column B has only two criterias ” Queso Cabrales” and “Scottish Longbreads” and column A would have Order ID like 10248,10249,10250 etc…. First, I need to select “Queso Cabrales ” in column B and navigate to Column A and autofilter select criteria “10248”. Now i want to copy the filtered data to another sheet starting from range B3. I need to iterate through each filter criteria available in Column A and copy paste the data to another sheet below the last filled data of each criteria.

    once it is done. I need to do the same process as above for “Scottish Longbreads” filter.

    Recorded macro and tried but the criteria in column A might be large and dynamic and lot of repeats would be there. Hence, I need the same in macro to execute it.Please suggest VBA help in it.

  • Hi Jon (All), good article – thank you.

    If I could get any assistance with the following I will be grateful.

    The following code achieves my objective:
    ‘ ** DynTbl_ALL filters correctly…
    DynTbl_ALL.Range.AutoFilter Field:=2, Criteria1:=Array(“LA – Loading Areas”, “=”, “(Blanks)”, “”), Operator:=xlFilterValues

    ‘ ** COPIES filtered values from DynTbl_ALL to DynTbl_SHAFT (a single column table) – All good just slow and inefficient…
    DynTbl_ALL.ListColumns(“SHAFT”).DataBodyRange.Copy DynTbl_SHAFT.ListColumns(“LA_SHAFT”).DataBodyRange

    ‘ ** This works well… DynTbl_SHAFT.ListColumns(“LA_SHAFT”).Range.RemoveDuplicates Columns:=1, Header:=xlNo

    ‘ ** This works well…
    DynTbl_SHAFT.ListRows.Add AlwaysInsert:=True

    ISSUE: This code is to comply with Rules of Coding Efficiency but don’t get the desired end result, as follows:

    ‘ ** DynTbl_ALL filters correctly…
    DynTbl_ALL.Range.AutoFilter Field:=2, Criteria1:=Array(“Loading Areas”, “=”, “(Blanks)”, “”), Operator:=xlFilterValues

    ‘ ** BETTER EFFICIENCY – ASSIGN (vs COPY) filtered values from DynTbl_ALL to DynTbl_SHAFT (a single column table) – BUT passes all data from the filtered column (visually the column is still filtered) – not achieving desired filtered data transfer outcome…
    DynTbl_SHAFT.ListColumns(“LA_SHAFT”).DataBodyRange.Value2 = DynTbl_ALL.ListColumns(“SHAFT”).DataBodyRange.Value2

    ‘ ** This works well… DynTbl_SHAFT.ListColumns(“LA_SHAFT”).DataBodyRange.RemoveDuplicates Columns:=1, Header:=xlNo

    ‘ ** This works well…
    DynTbl_SHAFT.ListRows.Add AlwaysInsert:=True

    Any assistance hereto will be greatly appreciated – thank you in advance…

  • Thank you for the article. But I think the section about using operators needs clarification. Maybe its my setup, but the autofilter didn’t work until I put the quotes around the operator (IE: “>=” 4/19/2021) as opposed to the entire selection IE: (“>=4/19/2021”)

  • Its really fascinating. I have a query. Is there any way that the filter can be applied for all values in the filter list successively (whatever the values available in the filter list), rather than specifying the exact cell value (like, rather than mentioning only “Product 3”, is there any way to filter all the values successively?)

  • Shouldn’t the ‘Sub Filter_Greater_Than_100()’ sub use the iCol variable instead of just using “Field:=5”?

  • Hello,

    I need help. I have made several autofilter buttons which refers to same columns, but different criteria filtering. The problem is that when I click on one button after another, it does not clear previous filter that is applied. Does anyone know solution? Thanks!

  • THIS IS BRILLIANT THANK YOU. Just a follow-up question, how do I use the value of a cell in the spreadsheet as criteria for the filter

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter