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

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

📌 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

📌 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

📌 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

📌 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

📌 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

📌 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

📌 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

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)