Auto Filter Between Dates – VBA Project
Is it possible to filter multiple criteria with an auto filter in a similar way to how the advanced filter works? Yes!
There are times when it is not possible to use an advanced filter in Microsoft Excel. However we can skilfully use an auto filter to do almost exactly the same. In this tutorial will cover many aspects that you will encounter in filtering data. I have provided a templates and all of the code for you to test and then modified for your own applications under development.
Will be looking at how to:
AutoFilter between dates
AutoFilter between dates as well as other criteria
AutoFilter multiple text fields
AutoFilter by month only
Lastly we will put together a project to show how we can do all of this from a userform application.
The auto filtered information is copied to our interface sheet so it appears very similar to the advanced filter in Microsoft Excel.
Note: You do not have to copy the data to the interface sheet but I think it is much easier to view and to deal with if you add this extra feature.
Added 6th March 2015
The dates in the template and the formatting in the code is for dates that have a forward slash (/) as the list separator. Check the regional settings in you computer if you are unsure of the separator or check how dates are currently shown in your files. You can also add a date serial number to a cell 42066 (3/3/2015) right click and choose Format and Date and assign the date with the Asterisk *. This will also show the default setting.
If you have a different list separator you will need to change the dates in the test data in the file to you regional date format and the code reference to the date format.
Download the Template
Video to filter between dates
Setting up the template
Adding the named ranges
These named ranges will be used for the list source of data validation.
The lists that they refer to are on the Lists worksheet.
Note: A dynamic named range must have data for it to work so make sure that you have values in these lists.
I have added one dynamic named range and the rest are static. You can change this to suit your needs. If you are not familiar with creating named ranges the mail suggest that you take the time to hone your skills with this marvellous aspect provided in Microsoft Excel.
Here are tutorials that will help.
To add these named ranges got to the Ribbon / Formulas / Name Manager / New and add the name in the Name box and the formula in the Refers To: box.
Test the named range by selecting it the Name Manager and clicking the Refers To: box on the right hand side. The range will be highlighted.
Do not proceed until these names are checked and working.
Adding the 2 formulas
There is only 2 formulas in this test application. Select the cell and then paste into the formula bar not the cell.
Add to cell F3of the Data Sheet
This formula adds the Frequency to the starting date to give us a due date.
Add to cell L3 of the Data sheet
This looks up the table that has the month and number and assigns a number to the month selected.
Adding data validation
Select the cell that you want to add the data validation to. On the ribbon click the Data tab and select Data Validation. Choose list and click in the source box then hit the F3 key to show the named ranges. Select the range you need from the list provided and click OK.
Add data validation lists to the following cells below:
Note: this is optional as you can type the values in if you want
Filter between 2 dates
This is the area we will be focusing on. If you enter the start date the Due date is calculated depending on the frequency added. Or if you want if you just type the dates into thedate cells.
Adding the code to the assorted module
This code will show all the data and remove the auto filter if an auto filter exists in that dataset.
'show all filtered data and remove filter
If Sheet2.AutoFilterMode Then
The code below will enable us to reset the auto filter and then to copy the values to the interface sheet. Think of it as a refresh button.
'show data and keep filter
If Sheet2.FilterMode Then
'copy the filtered data
Copy and paste the data to the Interface sheet
Firstly we clear the data on our interface sheet and then copy and paste be visible cells that had been filtered from our database.
'clear the contents
'copy and paste the range
Between 2 dates buttons
This is the code that does all of the work.
This code is explained extensively in the video above. Please take the time to watch that video and then step your way through the code.
Overview step by step
- Declare the variables(assign size in memory)
- Add error handling
- Set the variables
- Check dates entered
- Run the filter
- Copy the filtered data
- Show all data again
- Set the error block
'declare the variables
Dim Rng As Range
'set error handler
On Error GoTo errHandler:
'stop screen flicker
Application.ScreenUpdating = False
'set the variables
Set Rng = Sheet2.Range("C6")
DateBegin = Format(Sheet1.Range("D3").Value, "mm/dd/yy")
DateEnd = Format(Sheet1.Range("F3").Value, "mm/dd/yy")
'check the dates if all is OK run the filter
If Sheet1.Range("D3").Value >= Sheet1.Range("F3").Value Then
MsgBox " Your start value is wrong"
If Not IsEmpty(DateBegin) And Not IsEmpty(DateEnd) Then
'run the filter
.Autofilter Field:=9, Criteria1:=">=" & DateBegin, _
Operator:=xlAnd, Criteria2:="<=" & DateEnd
'show all data
On Error GoTo 0
MsgBox "There is no data"
In our next tutorial will have a look at filtering between dates and also adding to other fields to the criteria. This makes our auto filter extremely versatile.
You will be able to adapt these tutorials to any dataset that you might have if you take the time to understand what the code is doing rather than just copying and pasting it.