Visual Basic is a Microsoft language characterized by fulfilling functions that are complicated when trying them manually within the Office package. Among these, the term Autofilter allows filtering by date in an Excel table.
This automatic filter is used to show or hide data so that the user can focus on a group that needs to be given real importance.
Since the information does not disappear, the content can be added back to update the calculation results or clear the filter to see all the data again.
It is important to note that the auto filter is a method that can sort only on the column headings of a table.
Macro code to implement the AutoFilter method
If you decide to use the macro methodology to sort the data in a table format, you can modify the appearance and use of these through your coding. If you don’t know how we recommend that you previously learn how to create macros.
By adding conditional formatting to cells from a macro, you can even filter by color or filter multiple fields.
The sequence of terms is subject to the range of numerical 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 range of columns established as the field where the filter will be established.
Criteria1 are the established criteria between which the filter will be made. In this case, it is done as follows:
The ” _ ” is used to search for blank boxes, ” <> ” to search for boxes that have information, and ” > < ” to choose empty boxes as a type.
Performing this step is essential so that the filters do not occupy the entire range of the column.
The Operator is a constant recognized as ” XIAutoFilterOperator ” that specifies the type of filter to use.
Criteria2 is the second criterion, which works in conjunction with the two previous elements to build compound criteria.
In the case of wanting to filter by date in an Excel table, this is the only criterion in the date ranges to do it in date, month or year format.
To do this, a matrix must be followed in which the filtering “ Array (Level, Date) ” will be detailed where the level is the year, month or date and the date is the valid data within the period that will be filtered.
On the other hand, the Subfield is the field where the data type is found where the filter criteria are required to be applied.
Finally, the VisibleDropDown element defaults to the term True. True is used to display the drop-down arrow for the AutoFilter method in the selected field. On the other hand, if False is used, it is to hide it.
In the event that you decide to omit all the arguments, the encoding will only alter the display of the arrow that shows the auto filter over the range.
What should you do if you want to remove the auto filter?
It may happen that after filtering by date in an Excel table in a variable number of fields, you want to eliminate the effect in any of these columns.
To delete it, you just need to click on the drop-down menu of the column you want to change and click on “ Delete filter from (column name) ”.
If, on the other hand, you prefer to eliminate all the filters implemented in your table, you only need to select any cell, access the ” Data ” tab and click on ” Delete ” which is next to the ” Filter ” button.
Another easy method to remove all the autofilters that have been added to your table is to disable them completely.
This can be achieved by selecting the table to which you want to revert the effect and pressing the ” Autofilter ” button found in the Excel header.
Once you create all the macros you need, we recommend that you create your personal macro book.