How to filter tables in Excel using the Range.Autofilter method

Who I am
Philippe Gloaguen

Author and references

And let's continue with another interesting article on Microsoft Office Excel, this versatile program that allows you to create different projects , using graphs, tables and calculations and is also available online.

On this occasion, we will try to explain an advanced function, which will allow you to filter tables. This is why we will show you with simple steps how to filter tables in excel using the Range.Autofilter method.

How to filter tables in Excel using the Range.Autofilter method

We can filter the fields of a table in different ways or create advanced filters, but here we will explain how to do it through the metodo Range.Autofilter . With it we can filter a range by means of one or two criteria and it has a syntax with five optional arguments. Next we will show you how the syntax of Range.Autofilter is composed.

As you may have already noticed, the various advanced features of Exel allow us to use VBA codes. It is very different when you use its basic functions, such as reaching a cell directly in seconds.

How can I filter tables in Excel using the Range.Autofilter method

In the introduction to this article we have indicated that this method has a syntax which is as follows Range.Autofilter (field, testion1 , operator, test2, VicibleDropDown). The field argument will tell us which field to filter by. Criterion1 is a text string that will indicate the criteria with which we are going to filter and this must be written in quotation marks, with this method it is also possible to filter the tables.

Operator, will indicate the constant which will specify the filter type, Criterion2 is also a text string. And it is used with criterion1 and operator to create compound criteria, they are also used for unique criteria for fields in dates. VicibleDropDown is a real variant, which shows the drop down arrow of the autofilter.

Read Free Online Video Maker & Editor No Download

When it is false, it hides the arrow of the menu has downhill Automatic filter , but by default it is true, so that we can better visualize its use and application, we will do it through an example. Here we will have a table that consists of three columns, in column A we will have the name of Sellers, in column B the date of the sale and in column C the price.

Using VBA to filter tables

Here we have data from three different sellers and 20 sales have been made, which indicates that our table will have 21 rows and 3 columns.

Now we want filtration sales made by a particular seller, here we can choose any name. So, for that, we're going to go to the Visual Basic editor and go to the Developer tab.

We make a clip and then a clip in VBA and we will add a module to our book, for this we create a clip in Add Module. We will now write the following:

Secondary filter ()

Worksheets ("hoja1"). Rank ("A1"). Automatic filter

Field: = 1, _

Criteria: = "mary", _

VicibleDropDown: = False

End Sub

Now, the last thing we need to do is save the code and check if the statement we entered with the filter search was satisfied. To do this, let's minimize the current window and do turn the clip. Now let's check the sheet where our table is located, with the data of the sellers.

And in fact, we can verify that the filter was made by the saleswoman named María. Now suppose we want to filter for payments greater than or equal to 20 thousand, we will write the following code:

Secondary filter ()

Worksheets ("hoja1"). Rank ("A1"). Automatic filter

Field: = 3, _

Read How to write or insert text on an IMAGE in Word - Very easy (example)

Criteria: = "> 20000", _

Operator: = xlfiltervalue, _

VicibleDropDown: = True

End Sub

Finally we save and run, we can verify that the filter has been made for all those sales equal to or greater than 20 thousand. This simple way is possible filter tables in Excel using the Range.Autofilter method. And you learned it by following the steps we showed you in this interesting article.

add a comment of How to filter tables in Excel using the Range.Autofilter method
Comment sent successfully! We will review it in the next few hours.