How to filter on multiple fields using Excel macros

Who I am
Catherine Le Nevez
@catherinelenevez
Author and references

You shouldn't worry, as we understand that it is difficult code to understand, but you have found the ideal place with the simplest instructions to make it happen. In this case, a seven-column table with product data and prices from a store will be used as an example.

Method for filtering multiple fields using macros

To start performing this function, the " AutoFilter ”Which allows you to create a filter for a table or a field using two criteria.



In this case, the effect must be applied in more than one field. You need to apply the method on the same number of fields that you want to filter at one time.

What we want to establish through the criteria is to basically define what type of information will be shown and what will be hidden according to the criteria that are inserted in the code.

Step by step instructions with AutoFilter

In Microsoft Excel 2013 you can find the » Developer »In the header, which you will click to find the option» Visual Basic «.

 

Once in the code editor, an icon will appear on the project book with the option to create a new one " Module Which you have to press.

A blank sheet will appear on your screen on which you will start writing your macro code as follows.

Start by giving your macro a name, in this case it will be " Multiple filter Accompanied by both parentheses.

An advantage of the AutoFilter method is that it is just indicate the letter and cell number of the first range in so that it ends up covering the rest of the field.



For the first filter, cell A1 in the first sheet of the code will be used as the range, accompanied by the term » Automatic filter ". The next step is to specify the AutoFilter parameters, including the following terms:

  • The field will be set as the column you want to filter against. For example, the second.
  • The first criterion will be the term with which the filter will be governed to start showing or hiding the data, accompanied by " VisibleDropDown: = False «.

Add a new column to the macro code

Since you are trying to filter multiple fields Using macros, the second criterion you need to set in the VBA code is the next column that the filter will perform its effect on.

This repeats the step of establishing another filter within " Sub «, Placing the same characteristics of the sheet, modifying only the following terms:

  • The next field or column in which you want to apply the filter. For example, the fifth.
  • The first criterion of this second filter is the one that will establish the parameter in which the data summary will be carried out.
  • Include the term " Operator »To define the term by which the content will be filtered.
  • Finally, complete the creation of this second filter replacing » VisibleDropDown: = False «.

 


This way you will be able to filter multiple fields using macros, in this case between two fields that belong to your summary.


To check if it works, you just need to minimize the editor and go back to the tab " Developer »To find the option» Macro «.


Select the title of the macro code you just created. Click the »button Run »Which appears in the right part of the window.

This way you can appreciate how the rest of the content is automatically hidden and only the data that falls within the parameters of your criteria is displayed.


Protecting your documents is very important and if in this case your spreadsheets contain important information you should know that you can protect and remove them at will.

Once the tables and Excel spreadsheets are ready, you will be able to save them without any problem. You have the other option of converting your Excel spreadsheets to HTML or vice versa you can pass a PDF file to Excel.

You may find yourself having to print them later, so don't worry that you can do this easily and without damaging your work.

 

TagsEccellere
add a comment of How to filter on multiple fields using Excel macros
Comment sent successfully! We will review it in the next few hours.