Excel VBA: Filter Date Range Based on Cell Value (Macro and UserForm)

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.

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.

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.

Filter Date Range Based on Cell Value.xlsm

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.

<< Go Back to Date Filter | Filter in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!