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
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.
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.
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).
Similar Readings
- How to Use IF Formula for Date Range in Excel (6 Methods)
- SUMIF between Two Dates and with Another Criteria (7 Ways)
- Excel VBA: Filter Date Range Based on Cell Value (Macro and UserForm)
- Excel VBA: Filter Date before Today (With Quick Steps)
- How to Find Max Date in Range with Criteria in Excel
⧭ 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
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⧪ 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.
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.
