How to filter by date in an Excel table using the AutoFilter method

Who I am
Philippe Gloaguen
@philippegloaguen
Author and references

Since the information does not disappear, you can add the content again to update the calculation results or clear the filter to view all data again.

It is important to note that automatic filtering is one method that can sort on column headers only of a table.

Macro code to implement the AutoFilter method

If you decide to use the macro methodology to sort the data in table format, you can modify the appearance and use of these through your encoding. If you don't know how, we recommend that you learn how to create macros beforehand.



By adding conditional formatting to cells in a macro, you can even filter by color or filter multiple fields.

The sequence of the terms is subject to the range of numeric data that you will enter in the spreadsheet, so you must follow the formula of " Expression.AutoFilter (Faithful, Criteria1, Operator, Criteria2, SubField, VisibleDropDown) «.

Each of the elements can be defined as follows

Field It is a variant that refers to the established column range as the field in which the filter will be established.

Criteri1 are the established criteria among which the filter will be created. In this case, proceed as follows:

The " _ »Is used to search for empty boxes," <> »To search for boxes containing information and» > »To choose empty boxes as type.

Performing this step is essential to ensure that the filters do not occupy the entire range of the column.

THE operator is a constant recognized as " XIAutoFilterOperator »Which specifies the type of filter to use.


Criteria2 is the second criterion, which works in combination with the two previous elements to create composite criteria.


In case you want to filter by date in an Excel table, this is the only criterion in date ranges to do it in date, month or year format.

To do this it is necessary to follow a matrix in which the filter will be detailed " Array (Level, Date) ”Where the level is the year, the month or the date and the date is the valid data within the period to be filtered.

Il Subfield instead it is the field in which the data type in which the filter criteria are to be applied is found.

Finally, the element VisibleDropDown uses by default the term True. True is used to display the drop-down arrow for the AutoFilter method in the selected field. If instead you use False, is to hide it.

In case you decide to omit all arguments, the encoding will only change the display of the arrow showing the autofilter in the range.


What should you do if you want to remove the automatic filter?

It may happen that after having filtered by date in an Excel table in a variable number of fields, you want to eliminate the effect in one of these columns.

To delete it, simply click on the drop-down menu of the column you want to change and click on " Remove filter from (column name) ".

If, on the other hand, you prefer to delete all the filters implemented in your table, you just have to select any cell, access the tab » Data »And click on» Delete »Which is next to the button» Filter «.


Another simple way to remove all automatic filters that have been added to the table is to disable them completely.

This can be achieved by selecting the table to which you want to restore the effect and pressing the button » Automatic filter »Found in the Excel header.

Once you have created all the macros you need, we recommend that you create your own macro book.

TagsEccellere
add a comment of How to filter by date in an Excel table using the AutoFilter method
Comment sent successfully! We will review it in the next few hours.