And we continue with another interesting article about Microsoft Excel from Office, this versatile program that allows you to carry out different projects, using graphs, tables and calculations and is also available online.
In this opportunity, we will try to explain an advanced function that will allow you to filter tables. This is why we will show you with easy steps how to filter tables in Excel using the Range.Autofilter method.
We can filter the fields of a table in different ways or do advanced filters, but here we are going to explain how you can do it through the Range.Autofilter method. With it we can filter a range by means of one or two criteria and it has a syntax with five optional arguments. Later we will show you how the Range.Autofilter syntax is composed.
As you have already noticed, the different advanced features of Exel allow us to use VBA codes. It is very different when you use its basic functions, such as reaching directly to a cell in a matter of seconds.
How can I filter tables in Excel using Range.Autofilter method
In the introduction of this article we indicated that this method has a syntax that is the following Range.Autofilter (field, criterion1, operator, criterion2, VicibleDropDown). The field argument will tell us which field to filter by. Criteria1 is a text string that will indicate the criteria by which we are going to filter and this must be written between quotes, with this method you can also filter tables.
Operator, will indicate the constant that will specify the type of filter, Criterio2 is likewise a text string. And it is used with criterion1 and operator to create compound criteria, they are also used for unique criteria for the date fields. VicibleDropDown is a true variant, with which the AutoFilter dropdown arrow is displayed.
When it is false, it hides the AutoFilter dropdown arrow, 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 are going to 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 contain the data of three different sellers and 20 sales have been made, which indicates that our table will have 21 rows and 3 columns.
Now we want to make a filter on the sales that a particular seller has made, here we can choose any name. So for this, we are going to go to the Visual Basic editor and go to the Developer tab.
We clip and then clip in VBA and we’re going to add a module to our book, so we clip Add Module. Now we will write the following:
subfilter()
Worksheets (“sheet1”) . Range(“A1”) . auto filter
Field: =1, _
Criterion: =”mary”, _
VicibleDropDown := False
End Sub
Now, the last thing we need to do is save the code and check if the instruction that we have inserted with the filter search has been fulfilled. To do this, we minimize the current window and clip to run. Now we verify in the sheet where our table is located, with the data of the vendors.
And indeed, we can verify that the filter was carried out by the saleswoman named María. Now suppose we want to filter by payments greater than or equal to 20 thousand, we will write the following code:
subfilter()
Worksheets (“sheet1”) . Range(“A1”) . auto filter
Field: =3, _
Criteria: =”>20000”, _
Operator : = xlfiltervalue, _
VicibleDropDown := True
End Sub
To finish we save it and execute it, we can verify that the filter has been made for all those sales equal to or greater than 20 thousand. In this very simple way, you can filter tables in Excel using the Range.Autofilter method. And you learned it by following the steps that we have shown you in this interesting article.
