And we continue with another interesting article about Microsoft Office Excel, 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, 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.
We can filter the fields of a table in different ways or make 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 syntax for Range.Autofilter is composed.
As you have already noticed, the different advanced Exel functionalities allow us to use the VBA codes. It is very different when you use its basic functions, such as reaching a cell directly in a matter of seconds.
How can I filter tables in Excel using Range.Autofilter method
In the introduction to 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. Criterio1 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.
The operator will indicate the constant that will specify the type of filter, Criterion2 is, in the same way, a text string. And it is used with criteria1 and operator to create compound criteria, they are also used for unique criteria for fields on dates. VicibleDropDown is a true variant, which displays the AutoFilter drop-down arrow.
When it is false, it hides the Autofilter drop-down 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 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 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 filter on the sales made by a particular seller, 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 make a clip and then clip in VBA and we are going to add a module to our book, for this we make clip in Add module. Now we will write the following:
Sub filter ()
Worksheets (“sheet1”). Range (“A1”). Autofilter
Field: = 1, _
Criterial: = ”maría”, _
VicibleDropDown: = False
End Sub
Now, the last thing we have 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 we run. Now we check on the sheet where our table is located, with the data of the sellers.
And indeed, 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”). Range (“A1”). Autofilter
Field: = 3, _
Criterial: = ”> 20000”, _
Operator: = xlfiltervalue, _
VicibleDropDown: = True
End Sub
To finish we save and execute it, we can verify that the filter has been made for all those sales equal to or greater than 20 thousand. This simple way is that 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.