How to Filter Date Range in Pivot Table with Excel VBA

Get FREE Advanced Excel Exercises with Solutions!

In this article, I’ll show you how you can filter the date range of a pivot table with Excel VBA.

Filter Date Range in Pivot Table with Excel VBA (Quick View)

Sub Filter_Data_Range_in_Pivot_Table()

Starting_Date = CDate("1/18/2022")
Ending_Date = CDate("1/26/2022")

Set pvtT = ActiveSheet.PivotTables("PivotTable1")
pvtT.PivotFields("Order Date").Orientation = xlPageField

Set pvtF = Worksheets("Sheet1").PivotTables("PivotTable1").PivotFields("Order Date")

For Each pvtI In pvtF.PivotItems
    If DateValue(pvtI.Name) >= Ending_Date And DateValue(pvtI.Name) <= Ending_Date Then
        pvtI.Visible = True
    Else
        pvtI.Visible = False
    End If
Next pvtI

End Sub

VBA Code to Filter Date Range in Pivot Table with Excel VBA


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


How to Filter Date Range in Pivot Table with Excel VBA (Step-by-Step Analysis)

Here I’ve got a data set with the order date, product name, and order amount of some products of a company.

Data Set to Filter Date Range in Pivot Table with Excel VBA

Our objective is to first create a Pivot Table from this data set and then filter the date range in the table with Excel VBA.

I am showing you the step-by-step procedure to accomplish this.


⧭ Step 1: Creating the Pivot Table to Filter Date Range

First, we need to create the Pivot Table. Select the data set and fo the Insert > Pivot Table > From Table / Range button in the Excel toolbar. Click on From Table / Range.

Creating Pivot Table to Filter Date Range in Pivot Table with Excel VBA

A small dialogue box will open. Check Existing Worksheet. Then enter the cell reference of the cell where you want the Pivot Table.

Here it’s H2.

The Pivot Table will be created in the specified cell.


⧭ Step 2: Entering Rows and Values to the Pivot Table

Then you have to enter the rows and values into the Pivot Table. From the names of the columns available in the Pivot Table, drag the column Product Name to the field Row, and the column Order Amount to the field Value (Or you can accomplish this according to your needs).

Enter Rows and Values to Filter Date Range in Pivot Table with Excel VBA


Similar Readings


⧭ Step 3: Opening the Visual Basic Editor to Insert the VBA Code to Filter Date Range

The Pivot Table is now ready to use. Now we’ll focus on our main task. We’ll use VBA to filter the data in the table with a date range.

Press ALT + F12 on your keyboard to open the Visual Basic Window.

After the window opens, go to the Insert > Module option in the toolbar to insert a new Module. A module called Module1 will open.

We have to insert the code here.

⧭ Step 4: Entering the VBA Code

Now it’s time for entering the code. Enter the following VBA code in the recently opened Module.

⧭ VBA Code:

Sub Filter_Data_Range_in_Pivot_Table()

Starting_Date = CDate("1/18/2022")
Ending_Date = CDate("1/26/2022")

Set My_Table = ActiveSheet.PivotTables("PivotTable1")
My_Table.PivotFields("Order Date").Orientation = xlPageField

Set My_Field = Worksheets("Sheet1").PivotTables("PivotTable1").PivotFields("Order Date")

For Each My_Item In My_Field.PivotItems
    If DateValue(My_Item.Name) >= Ending_Date And DateValue(My_Item.Name) <= Ending_Date Then
        My_Item.Visible = True
    Else
        My_Item.Visible = False
    End If
Next My_Item

End Sub

VBA Code to Filter Date Range in Pivot Table with Excel VBA

For the sake of your understanding, I’ve divided the code into a few steps. Let’s see the step-by-step analysis of the code now.

⧪ Inserting the Starting Date and the Ending Date

First, we have to insert the starting date and the ending date of the range. We’ll insert it using the cDate function of VBA.

Starting_Date = CDate("1/18/2022")
Ending_Date = CDate("1/26/2022")

⧪ Adding the Order Date Columns as a Filter in the Pivot Table

Next, we’ll add the Order Date column as a filter in the Pivot Table.

Set pvtT = ActiveSheet.PivotTables("PivotTable1")
pvtT.PivotFields("Order Date").Orientation = xlPageField
[Here we’re considering the name of the Pivot Table as PivotTable1. You use your one.]

⧪ Removing the Rows of the Table that Fall outside the Date Range

This is the most important step. We’ll iterate through each row of the Pivot Table and remove the rows that fall outside the date range.

Set pvtF = Worksheets("Sheet1").PivotTables("PivotTable1").PivotFields("Order Date")

For Each pvtI In pvtF.PivotItems
    If DateValue(pvtI.Name) >= Ending_Date And DateValue(pvtI.Name) <= Ending_Date Then
        pvtI.Visible = True
    Else
        pvtI.Visible = False
    End If
Next pvtI

⧭ Step 5: Running the Code (Filtering Date Range with Excel VBA)

We’ve completed entering the code. Now it’s time for the final output. Again go to the Visual Basic toolbar and click on the Run Sub / UserForm button.

Running the Code to Filter Date Range in Pivot Table with Excel VBA

You’ll find only the data of the table that falls within your entered date range (1/18/2022 to 1/26/2022) being shown.

Read More: How to Use Pivot Table to Filter Date Range in Excel (5 Ways)


Things to Remember

While working with VBA in Excel, you needn’t save any file to run a VBA code. You can see the output of any code without saving the file. But in case you want to save the file for future use, you must save the file as Excel Macro-Enabled Workbook (*.xlsm).


Conclusion

So, this is the way to filter a date range in a Pivot Table with Excel VBA. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.


Related Articles

Rifat Hassan
Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo