Aung Shine
The Filter feature in Excel helps us to filter data by following specific conditions. And often, we have to Filter the data in our Excel worksheet based on certain Dates. Besides the Filter feature, we can also make use of Excel VBA to filter data. In this article, we’ll show you the effective ways to apply VBA Code to Filter Data by Date in Excel.
To illustrate, we are going to use a sample dataset as an example. For instance, the below dataset represents the Order ID, Product, and Delivery Date.
Download Practice Workbook
Download the following workbook to practice by yourself.
4 Examples of VBA Code to Filter Data by Date in Excel
1. VBA Code to Filter Data between Dates in Excel
Sometimes, we want to filter the data in our Excel worksheet based on a certain period of time. In the following dataset, we have a Start date and an End date. Here, we would like to apply the VBA Code to filter for the data present in the period between these two dates only. Therefore, follow the steps below to perform the task.
STEPS:
- First, select the sheet and right-click on the mouse.
- Now, select View Code.
- As a result, the VBA window will pop out and a dialog box will appear.
- After that, copy the following code and paste it into the box.
Sub BetweenDates()
Dim sDate As Long
Dim EDate As Long
sDate = Range("F5").Value
EDate = Range("G5").Value
Range("D4:D10").AutoFilter 1, ">=" & sDate, xlAnd, "<=" & EDate
End Sub
- Subsequently, press the F5 key to run the code.
- Next, close the VBA window.
- Finally, you’ll get the data present in that mentioned period.
Read More: VBA to Pivot Table Filter Between Two Dates in Excel
2. Filter Data by Exact Date with Excel VBA
However, we can also filter for data present on a specific date. In the below dataset, we have set an exact date in cell F5. So, follow the process to apply the VBA Code to Filter Data by Exact Date in Excel.
STEPS:
- In the beginning, select the View Code after right-clicking on the mouse.
- Consequently, the VBA window will appear and a dialog box will emerge.
- Now, copy the code and paste it there.
Sub FilterByExactDate()
Dim ExactDate As Date
Dim sDate As String
Dim EDate As Long
ExactDate = DateSerial(2022, 2, 5)
EDate = ExactDate
Range("D4:D10").AutoFilter
Range("D4:D10").AutoFilter Field:=1, Criteria1:=">=" & EDate, _
Operator:=xlAnd, Criteria2:="<" & EDate + 1
End Sub
- Afterward, press the F5 key.
- Close the VBA window.
- Eventually, it’ll return the data prevailing on that specified date.
Read More: How to Filter by Date in Excel (4 Quick Methods)
Similar Readings
- How to Filter in Excel with Merged Cells (Step-by-Step Procedure)
- Shortcut for Excel Filter (3 Quick Uses with Examples)
- Excel VBA: Filter Date Range Based on Cell Value (Macro and UserForm)
- Copy and Paste When Filter Is Applied in Excel
- How to Use Text Filter in Excel (5 Examples)
3. Apply VBA Code to Filter for Data before Specified One
Additionally, we may face some cases where we have to filter for data existing before a certain date. In this example, we have a specific date in cell F5. Hence, learn the following steps to carry out the operation.
STEPS:
- First of all, select the sheet.
- Then, right-click on the mouse and select View Code.
- Now the VBA window will emerge and a dialog box will appear.
- Next, copy the below code and paste it into the dialog box.
Sub BeforeDate()
Dim sDate As Long
sDate = Range("F5").Value
Range("D4:D10").AutoFilter 1, "<" & sDate
End Sub
- After that, press the F5 key and close the VBA window.
- In the end, it’ll return the data prevailing before that specified date.
Read More: How to Perform Custom Filter in Excel (5 Ways)
4. Filter for Data after Specified Date Using VBA Code
In our last example, we will show how to filter for data present after a specified date. Therefore, learn the process for applying the VBA Code to Filter Data by Date in Excel.
STEPS:
- Firstly, go to Sheet ➤ View Code.
- Consequently, a dialog box will appear in the pop-up VBA window.
- Then, copy the below code and paste it there.
Sub AfterDate()
Dim sDate As Long
sDate = Range("F5").Value
Range("D4:D10").AutoFilter 1, ">" & sDate
End Sub
- Afterward, press the F5 key as well as close the window.
- At last, it’ll return the desired data like it’s shown in the following picture.
Read More: How to Filter Data in Excel using Formula
Conclusion
Henceforth, you will be able to apply VBA Code to Filter Data by Date in Excel following the above-described examples. Keep using them and let us know if you have any more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
