Excel VBA: Filter Date before Today (With Quick Steps)

Get FREE Advanced Excel Exercises with Solutions!

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:

Filter Date Before Today Using VBA in Excel

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.

Filter Date Before Today Using VBA in Excel

Now, enter the range of your dataset. After that click on OK.

Filter Date Before Today Using VBA in Excel

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


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:

Filter Date after Today Using VBA in Excel

Now, select the range of cells of your dataset. And click on OK.

Filter Date after Today Using VBA in Excel

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

A.N.M. Mohaimen Shanto
A.N.M. Mohaimen Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

2 Comments
  1. 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.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo