Filtering Tables In Excel Using The Method Range.Autofilter

 excel table pc

And we continue with another interesting article on Microsoft Excel Office, this versatile program which allows different projects , using graphs, charts and calculations and is available also online .

This time, we will try to explain an advanced feature that allows users to filter tables. That is why we will show you with simple steps how to filter tables in Excel using the method Range.Autofilter.

 filter excel tables

You can filter fields in a table in different ways or make advanced filters but here we will explain how you can do it through the Range.Autofilter method . With it we can filter range by one or two criteria and has a syntax with five optional arguments. Later we will show how the syntax comprises Range.Autofilter.

As you noticed, the different functionalities advanced Exel , we can use VBA codes. It’s very different when you use its basic functions, such as reaching the directly to a cell in seconds.

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

The introduction of this article we indicate that this method has a syntax which is the next Range.Autofilter (field, criteria1, operator, criterion2, VicibleDropDown). The argument field will indicate by what seeps field. Criteria1 is a text string that will indicate the criteria by which we filter and it must be written in quotation marks, with this method you can also filter tables.

Operator, tell us constantly that we specify the filter type, is equally criterion2 a text string. And used with criteria1 and operator to create compound criteria are also used for only criteria for fields on dates. VicibleDropDown is a true variant, with which the AutoFilter drop-down arrow is displayed.

When is false, hides the drop-down arrow AutoFilter , but the default is true, so we can better visualize their use and application, we will do it through an example. Here we will have a table consisting of three columns, column A have a Vendor name in column B the date of sale and in column C the price.

Using the VBA for filters in tables

Here we contain data from three different vendors and 20 sales have been made, indicating that our board will have 21 rows and 3 columns.

Now we want to make a filter on sale has made a particular vendor, here we can choose any name. So for this, we will lead the Visual Basic editor and headed to the Developer tab.

We clip and then clip in VBA and we will add a module to our book, to do this we clip Add Module. Now write the following:

Sub filter ()

Worksheets ( “Sheet1”). Range ( “A1”). AutoFilter

Field: = 1, _

Criterial: = “Mary”, _

VicibleDropDown: = False

End Sub

Now, the last thing we should do is save the code and check whether the instruction is fulfilled we have inserted with the search filter. To do this, we minimize the current window and we clip run . Now we check on the sheet where our table with data vendors.

And indeed, we can see that the filter is made by the seller, which is called Mary. Now suppose we want to filter by payments greater than or equal to 20 thousand, write the following code:

Sub filter ()

Worksheets ( “Sheet1”). Range ( “A1”). AutoFilter

Field: = 3, _

Criterial: = “& gt; 20000”, _

Operator: = xlfiltervalue, _

VicibleDropDown: = True

End Sub

 using excel table

To complete the store and execute, we can see that the filter has made all those sales equal to or greater than 20 mil. Thus simple as it is that you can filter tables in Excel using the method Range.Autofilter And you learn by following the steps we’ve shown in this interesting article


How useful did you find this content?

Click on a star to rate!

Average score 0 / 5. Counting of votes: 0

So far, no votes. Be the first to rate this content.

I'm sorry this content was not useful for you!

Let me improve this content!

Tell me, how can I improve this content?

Deja un comentario