In this article, I’ll show you how you can filter a date range from a given data set based on a cell value using VBA in Excel.
Excel VBA: Filter Date Range Based on Cell Value (Quick View)
Sub Filter_Date_Range()
Set First_Cell = Worksheets("Sheet1").Range("B3")
Set Starting_Date = Range("C14")
Set Ending_Date = Range("C15")
Field = 1
First_Cell.AutoFilter Field:=Field, Criteria1:=">" & Starting_Date, Operator:=xlAnd, Criteria2:="<" & Ending_Date
End Sub
An Overview of the VBA Code to Filter Date Range Based on Cell Value in Excel
Here we’ve got a worksheet with a dataset in the range B3:D12 that contains the Delivery Dates, Delivery Items, and Quantities of a company. Along with the data set, we have two more dates called the Starting Date and the Ending Date in cells C14 and C15 respectively.
Our objective is to filter the dataset based on these 2 dates. That is, we’ll keep only those rows where the Delivery Date falls within these 2 dates.
I’m showing you the step-by-step breakdown of the VBA code for your understanding.
⧪ Step 1: Inserting the Inputs
First of all, we have to insert the inputs into the code. There are 4 inputs required in this code.
- The first cell of the data set (Cell B3 of worksheet Sheet1 in this example).
- The starting date (Cell C14 in this example)
- The ending date (Cell C15 in this example)
- The field (The number of the column in the data set based on which we’ll filter. 1 in this example, Delivery Date).
Set First_Cell = Worksheets("Sheet1").Range("B3")
Set Starting_Date = Range("C14")
Set Ending_Date = Range("C15")
Field = 1
⧪ Step 2: Applying AutoFilter Method of VBA
This is the most important step. We’ll apply the AutoFilter method of VBA to filter the data set based on the starting date and the ending date.
We want to filter the dates between the starting date and the ending date. That means we want to keep only the rows where the delivery date is greater than or equal to the starting date and less than or equal to the ending date.
Therefore, we have to consider two criteria here. The first criterion will denote the dates that are greater than or equal to the starting date, and the second criterion will denote the dates that are less than or equal to the ending date.
And we’ll join the two criteria by an AND operator (xlAnd).
First_Cell.AutoFilter Field:=Field, Criteria1:=">" & Starting_Date, Operator:=xlAnd, Criteria2:="<" & Ending_Date
Therefore, the complete VBA code will be:
⧠VBA Code:
Sub Filter_Date_Range()
Set First_Cell = Worksheets("Sheet1").Range("B3")
Set Starting_Date = Range("C14")
Set Ending_Date = Range("C15")
Field = 1
First_Cell.AutoFilter Field:=Field, Criteria1:=">" & Starting_Date, Operator:=xlAnd, Criteria2:="<" & Ending_Date
End Sub
How to Filter Date Range Based on Cell Value with Excel VBA: 2 Suitable Methods
In this section, we will discuss two effective methods to filter date ranges based on cell values using VBA Macro Code and developing a Userform.
1. Developing a Macro to Filter Date Range Based on Cell Value in Excel
We’ve seen the step-by-step analysis of the code to filter a date range based on some cell values. Now, let’s see how we can develop a Macro to run the code.
⧪ Step 1: Opening VBA Window
Press ALT + F11 on your keyboard to open the Visual Basic window.
⧪ Step 2: Inserting a New Module
Go to Insert > Module in the toolbar. Click on Module. A new module called Module1 (or anything else depending on your past history) will open.
⧪ Step 3: Putting the VBA Code
This is the most important step. Insert the given VBA code in the module.
⧪ Step 4: Running the Code
Click on the Run Sub / UserForm tool from the toolbar above.
⧪ Step 5: The Output
If you can run the Macro successfully, you’ll find the date set in your desired worksheet has been filtered based on your Starting Date and the Ending Date.
Read More: How to Filter Last 30 Days of Date in Excel
2. Building a UserForm to Filter Date Range Based on Cell Value in Excel
If you wish, you can also build a UserForm to filter a date range based on some cell values using VBA.
Follow the given step-by-step procedure mentioned below.
⧪ Step 1: Opening VBA Window
Press ALT + F11 on your keyboard to open the Visual Basic window (Same as the 1st step of developing Macro).
⧪ Step 2: Inserting a New UserForm
Go to Insert > UserForm in the toolbar. Click on UserForm. A new UserForm called UserForm1 will open along with a Toolbox.
⧪ Step 3: Dragging Tools to the UserForm
Next, you have to drag tools to the UserForm from the ToolBox. Drag 5 Labels, 2 ListBoxes, 3 TextBoxes, and 1 CommandButton as shown in the image.
Change the displays of the Labels and the CommandButton as shown.
⧪ Step 4: Adding Code to ListBox1
Double-click on ListBox1. A private sub-procedure called ListBox1_Click will open. Insert the following VBA code there.
For i = 0 To UserForm1.ListBox1.ListCount - 1
   If UserForm1.ListBox1.Selected(i) = True Then
       Worksheets(UserForm1.ListBox1.List(i)).Activate
   End If
Next i
On Error GoTo LB1
Range(UserForm1.TextBox1.Text).Select
UserForm1.ListBox2.Clear
i = 1
While Range(UserForm1.TextBox1.Text).Cells(1, i) <> ""
   UserForm1.ListBox2.AddItem i
   i = i + 1
Wend
LB1:
   Error_Value = 240
⧪ Step 5: Adding Code to TextBox1
Next double-click on TextBox1. Another private sub-procedure called TextBox1_Change will open. Insert the following VBA code there.
On Error GoTo TB1
Range(UserForm1.TextBox1.Text).Select
UserForm1.ListBox2.Clear
i = 1
While Range(UserForm1.TextBox1.Text).Cells(1, i) <> ""
   UserForm1.ListBox2.AddItem i
   i = i + 1
Wend
TB1:
   Error_Value = 240
⧪ Step 6: Adding Code to TextBox2
Next double-click on TextBox2. Another private sub-procedure called TextBox2_Change will open. Insert the following VBA code there.
On Error GoTo TB2
Range(UserForm1.TextBox2.Text).Select
TB2:
   Error_Value = 240
⧪ Step 7: Adding Code to TextBox3
Again double-click on TextBox3. Another private sub-procedure called TextBox3_Change will open. Insert the following VBA code there.
On Error GoTo TB3
Range(UserForm1.TextBox3.Text).Select
TB3:
   Error_Value = 240
⧪ Step 8: Adding Code to CommandButton1
Finally, double-click on CommandButton1. Another private sub-procedure called CommandButton1_Click will open. Insert the following VBA code there.
Set First_Cell = ActiveSheet.Range(UserForm1.TextBox1.Text)
Set Starting_Date = ActiveSheet.Range(UserForm1.TextBox2.Text)
Set Ending_Date = ActiveSheet.Range(UserForm1.TextBox3.Text)
For i = 0 To UserForm1.ListBox2.ListCount
   If UserForm1.ListBox2.Selected(i) = True Then
       Field = UserForm1.ListBox2.List(i)
   End If
Next i
First_Cell.AutoFilter Field:=Field, Criteria1:=">" & Starting_Date, Operator:=xlAnd, Criteria2:="<" & Ending_Date
⧪ Step 9: Inserting Code for Running the UserForm
We’ve reached the last stage of our procedure. We’ll finish by inserting a code for running the UserForm.
Insert a new module from the toolbar and insert the following VBA code there.
Sub Run_UserForm()
UserForm1.Caption = "Filter Date Range Based On Cell Value"
UserForm1.ListBox1.BorderStyle = fmBorderStyleSingle
UserForm1.ListBox2.BorderStyle = fmBorderStyleSingle
UserForm1.ListBox1.ListStyle = fmListStyleOption
UserForm1.ListBox2.ListStyle = fmListStyleOption
For i = 1 To Sheets.Count
   UserForm1.ListBox1.AddItem Sheets(i).Name
   If Sheets(i).Name = ActiveSheet.Name Then
       UserForm1.ListBox1.Selected(i - 1) = True
   End If
Next i
UserForm1.TextBox1.Text = Selection.Address
UserForm1.ListBox2.Clear
i = 1
While Range(UserForm1.TextBox1.Text).Cells(1, i) <> ""
   UserForm1.ListBox2.AddItem i
   i = i + 1
Wend
Load UserForm1
UserForm1.Show
End Sub
⧪ Step 10: Running the UserForm
Our UserForm is now ready to use. Run the Macro called Run_UserForm.
The UserForm will be loaded with the name Filter Date Range Based On Cell Value. Insert the necessary inputs into it.
Here I’ve selected Sheet1 as the worksheet, cell B3 as the first cell of the range, 1 as the field, cell C14 as the starting date, and cell C15 as the ending date.
You set these according to your needs.
Finally, click on OK. You’ll find the date set in your desired worksheet has been filtered based on your Starting Date and the Ending Date.
Read More: How to Filter Dates by Month and Year in Excel
Things to Remember
Here we’ve used 2 AND type criteria to filter the data set using a single Field. If you wish, you can use more than 2 criteria with different Fields. Also if you want to use OR type operation other than AND type, use xlOr as the operator in place of xlAnd. Check the AutoFilter method of VBA here for more details.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
Therefore, this is the process of filtering a date range based on some values with VBA in Excel. Do you have any questions? Feel free to ask us.
