In Microsoft Excel, working with dates is a pretty common thing. And filtering those dates is also a day-to-day task for a lot of Excel users. Now, we can traditionally filter them. But, with Excel, we can do this a lot faster. Also, we can process a lot of data easily with VBA codes. In this tutorial, you will learn to filter the date before today using VBA in Excel.
This tutorial will be on point with suitable examples and proper illustrations. So, read the article to enrich your Excel knowledge.
Download Practice Workbook
Step By Step Procedure to Filter Date before Today Using VBA in Excel
In the following sections, we will show you a step-by-step guide to building a VBA code that will filter the dates before today in your Excel worksheet. By those procedures, we will cover each line with explanations. If you read that, you will easily solve the problem next time. It will develop your Excel knowledge.
Have a look at the dataset:
Here, we have a dataset that contains some person’s name, their buying product, the amount, and the due date. Now, while I am writing the article, the current date is March 22, 2022.
Those people have to pay their amount on their respective due date. As you can see, there are some dates in the dataset before March 22, 2022. Now, we have to filter our dataset and find those results that are before today using the VBA code. Let’s get into it and solve it together.
Build the Code to Filter Date before Today Using VBA
📌 Open VBA Editor
- First, press Alt+F11 on your Keyboard to open the VBA editor.
- Then, select Insert>Module.
📌 Create Sub Procedure
Sub filter_date_before_today()
End Sub
In this procedure, we will write the entire code.
📌 Declare Necessary Variables
Sub filter_date_before_today()
Dim date_range As Range
End Sub
The date_range variable will contain the range of cells of our entire dataset.
📌 Set the Range Containing Dates
Sub filter_date_before_today()
Dim date_range As Range
On Error Resume Next
Set date_range = Application.InputBox("Enter The Range of Your Dataset", Type:=8)
End Sub
On Error Resume Next : Now, the On Error Resume Next statement tells VBA to ignore any lines of code having errors, and proceed immediately to the following line of code. After that, the Excel VBA codes will skip the line or lines that contain errors in them, and move on to the following sequence of code.
Application.InputBox: It will show an input box.
Type:=8: Type determines what type of object we are taking. Here, 8 means, we are providing a range object.
📌 Check Whether the Range Is Valid or Not
Sub filter_date_before_today()
Dim date_range As Range
On Error Resume Next
Set date_range = Application.InputBox("Enter The Range of Your Dataset", Type:=8)
If date_range Is Nothing Then Exit Sub
End Sub
If date_range Is Nothing Then Exit Sub: This line will check whether the user is providing a valid range or not. If there is no range, then it will exit the subprocedure.
📌 Turn Off Screen Updating
Sub filter_date_before_today()
Dim date_range As Range
On Error Resume Next
Set date_range = Application.InputBox"Enter The Range of Your Dataset", Type:=8)
If date_range Is Nothing Then Exit Sub
Application.ScreenUpdating = False
End Sub
Application.ScreenUpdating = False: By setting the value to False, we are making our VBA code faster. We won’t be able to see what the macro is doing in the background. Remember, you have to set this value to True at the end.
📌 Remove Autofilter
Sub filter_date_before_today()
Dim date_range As Range
On Error Resume Next
Set date_range = Application.InputBox("Enter The Range of Your Dataset", Type:=8)
If date_range Is Nothing Then Exit Sub
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
End Sub
ActiveSheet.AutoFilterMode = False: By default it is True. If it is True, it displays the drop down arrows. But, by setting the value to False we are removing all the drop down arrows.
📌 Set Working Region
Sub filter_date_before_today()
Dim date_range As Range
On Error Resume Next
Set date_range = Application.InputBox("Enter The Range of Your Dataset", Type:=8)
If date_range Is Nothing Then Exit Sub
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
If date_range.Count = 1 Then
Set date_range = date_range.CurrentRegion
End If
End Sub
If date_range.Count = 1 Then: It checks your given range has cells or not. If it is True, it will proceed to next.
Set date_range = date_range.CurrentRegion: It will set the whole range for filtering.
📌 Filter Date before Today
Sub filter_date_before_today()
Dim date_range As Range
On Error Resume Next
Set date_range = Application.InputBox("Enter The Range of Your Dataset", Type:=8)
If date_range Is Nothing Then Exit Sub
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
If date_range.Count = 1 Then
Set date_range = date_range.CurrentRegion
End If
date_range.AutoFilter Field:=4, Criteria1:="<" & CDbl(Date)
End Sub
Field:=4: Here, we are filtering our entire dataset. Here, 4 is our Field. It indicates the 4th column of our dataset. That is the Due Date column.
Criteria1:=”<“: It is our criteria. By using this, we are comparing our dates with today.
CDbl(Date): This function converts a value to double. In non-English Excel versions, sometimes the DateTime value doesn’t work. For that reason, we use an additional CDbl. Here, the Date refers to the current date of your computer system.
📌 Turn Off Screen Updating
Sub filter_date_before_today()
Dim date_range As Range
On Error Resume Next
Set date_range = Application.InputBox("Enter The Range of Your Dataset", Type:=8)
If date_range Is Nothing Then Exit Sub
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
If date_range.Count = 1 Then
Set date_range = date_range.CurrentRegion
End If
date_range.AutoFilter Field:=4, Criteria1:="<" & CDbl(Date)
Application.ScreenUpdating = True
End Sub
Finally, set your ScreenUpdating to its default value True.
Run the Code in Excel Worksheet
I hope you followed along with our guide to build the code. Now, it’s time to check whether VBA codes work successfully or not.
First, press Alt+F8 to open the Macro dialog box.
Now, select filter_date_before_today and click on Run.
Now, enter the range of your dataset. After that click on OK.
Now, look at the dataset. The date of today is March 22, 2022. In the dataset, we have only those dates that are before today’s date. So, our VBA worked successfully to filter the date before today in Excel.
Read More: How to Filter Date Range in Pivot Table with Excel VBA
Similar Readings
- How to SUMIF between Two Dates and with Another Criteria (7 Ways)
- Use IF Formula for Date Range in Excel (6 Methods)
- VLOOKUP Date Range and Return Value in Excel (4 Suitable Methods)
- How to Set Due Date Reminder in Excel (3 Quick Methods)
- Use Formula for Past Due Date in Excel (3 Methods)
Filter Date after Today with Excel VBA
Now, if you have built the previous code along with me, then you can easily solve this without a doubt. You have to make a slight change.
The following VBA code will filter the date after Today in Excel:
Sub filter_date_after_today()
Dim date_range As Range
On Error Resume Next
Set date_range = Application.InputBox("Enter The Range of Your Dataset", Type:=8)
If date_range Is Nothing Then Exit Sub
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
If date_range.Count = 1 Then
Set date_range = date_range.CurrentRegion
End If
date_range.AutoFilter Field:=4, Criteria1:=">" & CDbl(Date)
Application.ScreenUpdating = True
End Sub
Here, we have just changed the criteria.
After running the code, you will see the following:
Now, select the range of cells of your dataset. And click on OK.
As you can see from the screenshot, our VBA code successfully worked to filter the date before today in Excel. With almost the same code, we solved both problems. Now, practice this by yourself.
Read More: How to Filter Date Range in Excel (5 Easy Methods)
💬 Things to Remember
✎ CDbl(Date) returns a double value. Excel stores the dates as doubles. Here, the Date inside the CDbl function refers to the current date of your computer system.
✎ Make sure your Field number matches the column number by which you want to filter. Here, we set the Field to 4 as our Due Date column is the fourth column in our dataset.
Conclusion
To conclude, I hope this tutorial has provided you with a piece of useful knowledge to filter the date before today in Excel using VBA codes. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.
Don’t forget to check our website ExcelDemy.com for various Excel-related problems and solutions.
Keep learning new methods and keep growing!
Related Articles
- How to Filter Dates by Month and Year in Excel (4 Easy Methods)
- How to Use Custom Date Filter in Excel (5 Easy Ways)
- Excel VBA: Filter Date Range Based on Cell Value (Macro and UserForm)
- How to Filter Last 30 Days of Date in Excel (5 Easy Ways)
- VBA Code to Filter Data by Date in Excel (4 Examples)
- How to Use Pivot Table to Filter Date Range in Excel (5 Ways)
Hi,
It was really good content however, i have a different ask..my criteria is to filter the data from last 5 months with todays date.. and this i have to do every month.. another criteria is where the data needs to be filtered with more than past 5 months with today’s date..
Hi Vikas,
Seems like your first problem meets the criteria for this article . In the ending date cell, try using the TODAY function, and at the starting date put in the formula =EDATE(TODAY(),-5).
As for your second criteria, use the formula =EDATE(TODAY(),-5) of this article’s current cell value instead of today’s date and that should do the trick.
Hope that helped. If you are still facing issues, or you wanted a different result let us know.