How to filter tables in Excel using the Rangeautofilter method

Who I am
Catherine Le Nevez
Author and references

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 one or two criteria and it has a syntax with five optional arguments. Next we will show you how the syntax for 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, criterion1, operator, criterion2, VicibleDropDown). The field argument will tell us which field to filter from. Criterion1 is a text string that will indicate the criteria with which we are going to filter and this must be written in quotes, with this method you can also filter the tables.

Operator, will indicate the constant that will specify the type of filter, 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 true variant, which displays the autofilter drop-down arrow.

When false, it hides the drop-down arrow of the 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 consisting of three columns, in column A we will have the name of the 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 on sales made by a particular seller, we can choose any name here. So, for that, we're going to go to the Visual Basic editor and go to the Developer tab.

We create 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:

Sub-filter ()

       Worksheets ("sheet1"). Gamma ("A1"). Automatic filter

        Field: = 1, _

        Criterion: = ”mary”, _

        VicibleDropDown: = False

End subtitle

Now, the last thing we need to do is save the code and check if the statement we entered with the search filter was satisfied. To do this, let's minimize the current window and do perform 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:

Sub-filter ()

   Worksheets ("sheet1"). Gamma ("A1"). Automatic filter

        Field: = 3, _

        Criterion: = ”> 20000”, _

        Operator: = xlfiltervalue, _

        VicibleDropDown: = True

End subtitle

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

TagsEccellere report this ad
add a comment of How to filter tables in Excel using the Rangeautofilter method
Comment sent successfully! We will review it in the next few hours.