Excel Pivot Tables Grouping: Group Items, Group Data and Group Date Values, using VBA
You can create a group containing the selected items, in the Rows & Columns Areas of a PivotTable report. Grouping data bunches rows or columns the way you want and which might not be possible with the PivotTable tools like sorting or filtering.
Related Links:
1. Create an Excel Pivot Table report using VBA
2. Referencing an Excel Pivot Table Range using VBA
3. Reference Pivot Fields and Pivot Items in an Excel Pivot Table, using VBA
4. Excel Pivot Table Address, Location, Move & Copy using VBA
5. Excel Pivot Table Layout and Design, using VBA
6. Excel Pivot Table Properties & Settings, using VBA
7. Refresh Excel Pivot Table and Cache of PivotTable, using VBA
8. Excel Pivot Tables Grouping: Group Items, Group Data and Group Date Values, using VBA
9. Excel Pivot Tables: Sort Fields, Values & Dates, use Custom Lists, with VBA
10. Excel Pivot Tables: Filter Data, Items, Values & Dates using VBA
11. Excel Pivot Tables: Summary Functions, Custom Calculations & Value Field Settings, using VBA
12. Excel Pivot Tables: Insert Calculated Fields & Calculated Items, Create Formulas using VBA
13. Create & Customize Excel Pivot Table Charts, using VBA
Refer complete Tutorial on using Pivot Tables in Excel Window (user interface):
Create and Customize a Pivot Table report
——————————————————————————————-
Contents:
Group Date Values in a PivotTable report, using the Group method
Group Date Values by Weeks in a PivotTable report
Group Numeric Items in a PivotTable report, using the Group method
Group Specific Text Field Items in a PivotTable report
——————————————————————————————-
Use the Group Method to group numeric items or date values. Syntax: RangeObjectVariable.Group(Start, End, By, Periods). All 4 arguments of Start, End, By & Periods are optional and explained below.
Start – the grouping starts from this value and if omitted or True indicates the first value in the field.
End – the grouping ends at this value and if omitted or True indicates the last value in the field.
By – specifies the group size for a numeric field; for a date field it indicates the number of days in case array element 4 (Days) is set to True and all other elements are False in the Periods argument, else this argument gets ignored and a default size is chosen for the group.
Periods – an array of 7 Boolean values: array element 1 indicates the Period of Seconds; element 2 is Minutes, element 3 is Hours, element 4 is Days, element 5 is Months, element 6 is Quarters and element 7 is Years. An element having a Bollean value of True indicates the period for which the Group is created. This argument of Periods is valid only in respect of Date fields.
It may be noted that the RangeObject should only be a single cell otherwise the method will fail without showing any error.
1. Group Date Values in a PivotTable report, using the Group method
Example 1: Example of grouping Date cells. Refer Image 1 which shows a PivotTable report on the left showing the Dates field which contains date values. The Pivot report on the right groups the Dates Field which now shows as 3 fields representing Quarters, Months and Dates (elements 4, 5 & 6 are set to True in the Periods argument), using the below code.
Sub PivotTableGroupData1()
Dim PvtTbl As PivotTable
Dim rngGroup As Range
Set PvtTbl = Worksheets(“Sheet6”).PivotTables(“PivotTable1”)
‘set range of dates to be grouped
Set rngGroup = PvtTbl.PivotFields(“Dates”).DataRange
‘rngGroup.Cells(1) indicates the first cell in the range of rngGroup – remember that the RangeObject in the Group Method should only be a single cell otherwise the method will fail.
rngGroup.Cells(1).Group Periods:=Array(False, False, False, True, True, True, False)
‘to ungroup:
‘rngGroup.Cells(1).Ungroup
End Sub
2. Group Date Values by Weeks in a PivotTable report
Grouping Date cells by Weeks: There is no array element in the Periods argument (of the Group method) corresponding to Weeks. To group by weeks, you need to set array element 4 (ie. Days) to True in the Periods argument and all other elements as False, and specify 7 in the By argument which sets the number of days for the group size.
However, the week range is determined by the first date to be grouped. To have a custom start date for the week range, enter the date in the start argument. To have the weekly range start from a specific day, say Monday, use the excel Weekday function to adjust the start day of the week range. (see below example).
Note that grouping dates by week will not allow another period grouping simultaneously ie. you will not be able to group by Months if you have grouped by Weeks.
Example 2: To have the week range for grouping start from the first Monday preceding the first date in the PivotField of Dates – refer Image 2 which shows the PivotTable report before grouping on the left and the grouped PivotTable on the right side after running the below code.
Sub PivotTableGroupData2()
Dim PvtTbl As PivotTable
Dim rngGroup As Range
Dim fDate As Date
Dim sDate As Date
Dim n As Integer
Set PvtTbl = Worksheets(“Sheet12”).PivotTables(“PivotTable1”)
‘set range of dates to be grouped
Set rngGroup = PvtTbl.PivotFields(“Dates”).DataRange
‘determine the first date in the PivotField of Dates
PvtTbl.PivotFields(“Dates”).AutoSort Order:=xlAscending, Field:=“Dates”
fDate = PvtTbl.PivotFields(“Dates”).DataRange.Cells(1).value
‘determine the number of days to be deducted from the first date to arrive at the first preceding Monday
n = Application.Weekday(fDate, 3)
‘calculate the start date for grouping which will be the first Monday preceding the first date
sDate = fDate – n
rngGroup.Cells(1).Group by:=7, Start:=sDate, Periods:=Array(False, False, False, True, False, False, False)
‘to ungroup:
‘rngGroup.Cells(1).Ungroup
End Sub
3. Group Numeric Items in a PivotTable report, using the Group method
Use the Group Method to group numeric items or date values: In this example we are also using the PivotField.LabelRange Property. This property returns the range or cell(s) that contain the Field Label. The LabelRange property is being used because the RangeObject in the Group Method should only be a single cell otherwise the method fails. The LabelRange property enables selection of a single cell as the RangeObject in the Group Method.
Example 3: Example of grouping numeric items – refer Image 3 which shows a PivotTable report on the left showing the numeric items in the “Months” field. The Pivot report on the right groups the Months into group size of 3 months each, starting from month 1 and ending at month 12, using the below code.
Sub PivotTableGroupData3()
Dim PvtTbl As PivotTable
Dim rngLabel As Range
Dim rngGroup As Range
Set PvtTbl = Worksheets(“Sheet10”).PivotTables(“PivotTable1”)
‘set range of field label whose items are to be grouped:
Set rngLabel = PvtTbl.PivotFields(“Months”).LabelRange
rngLabel.Group Start:=1, End:=12, by:=3
‘to ungroup:
‘rngLabel.Ungroup
End Sub
4. Group Specific Text Field Items in a PivotTable report
A part of the PivotTable report can be selected using the PivotTable.PivotSelect Method. This method has 3 arguments – Name, Mode & UseStandardName. Name argument is mandatory to specify while other arguments are optional. Name argument represents the part of the report to be selected. Mode specifies the selection mode for type of items to be selected – options are xlBlanks, xlButton, xlDataAndLabel, xlDataOnly, xlFirstRow, xlLabelOnly and xlOrigin viz. to select labels use xlLabelOnly.
Example 4: Example of grouping text field items – refer Image 4 which shows the PivotTable report on the left before grouping. The Pivot report on the right groups the 2 cities of “Leeds” & “London” in the “City” field, and gives the group name “GroupUK”, using the below code.
Sub PivotTableGroupData4()
Dim PvtTbl As PivotTable
Dim pvtItm As PivotItem
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)
‘loop through all items in the PivotTable Field of “City” to hide or show them – all cities/items are hidden except Leeds & London:
For Each pvtItm In PvtTbl.PivotFields(“City”).PivotItems
If MsgBox(“Hide Item ” & pvtItm & “?”, vbYesNo) = vbYes Then
pvtItm.Visible = False
End If
Next
‘select all items which are visible:
Application.PivotTableSelection = True
PvtTbl.PivotSelect “City”, xlLabelOnly
‘group the selected items:
Selection.Group
‘change the group name from the default “Group1”:
For Each pvtItm In PvtTbl.PivotFields(“City2”).PivotItems
If pvtItm.Visible = True Then
pvtItm.Name = “GroupUK”
End If
Next
‘show all items in the grouped Pivot Field:
For Each pvtItm In PvtTbl.PivotFields(“City2”).PivotItems
pvtItm.Visible = True
Next
‘show all items of the Pivot Field in which specific items were grouped:
For Each pvtItm In PvtTbl.PivotFields(“City”).PivotItems
pvtItm.Visible = True
Next
End Sub