Show All
GroupOn property as it applies to the PivotField object.
Returns or sets a PivotFieldGroupOnEnum constant that represents the grouping settings for a field. Use this property to determine how items are grouped in a PivotTable field. Read/write.
| PivotFieldGroupOnEnum can be one of these PivotFieldGroupOnEnum constants. |
| plGroupOnEachValue Specifies no grouping. The default setting. |
| plGroupOnPrefixChars Group text values by the first N characters of each value, where N is the GroupInterval property value. This setting is valid only for textual fields. |
| plGroupOnYears Group date or time values by years. |
| plGroupOnQtrs Group date or time values by quarters. |
| plGroupOnMonths Group date or time values by month. |
| plGroupOnWeeks Group date or time values by week. |
| plGroupOnDays Group date or time values by their respective day number |
| plGroupOnHours Group date or time values by their respective hour number. |
| plGroupOnMinutes Group date or time values by their respective hour number. |
| plGroupOnSeconds Group date or time values by their respective second number. |
| plGroupOnInterval Group numeric values by the interval specified in the GroupInterval property. |
expression.GroupOn
expression Required. An expression that returns a PivotField object.
Once you have set the GroupOn property, you can use the GroupInterval, GroupStart, and GroupEnd properties to further define how to group items in a field.
Setting this property affects the way in which values in each field are displayed on the row, column, and filter areas of a PivotTable list. The settings for this property do not affect how values are displayed in the detail area of a PivotTable list. However, the filter dropdown will reflect the GroupOn setting for a field in the detail area of a PivotTable list.
Setting this property of a PivotTable field clears its IncludedMembers and ExcludedMembers properties if they have been set.
GroupOn property as it applies to the GroupLevel and PageField objects.
Returns or sets a DscGroupOnEnum that represents the way GroupLevel objects or PageField objects are grouped. Read/write.
expression.GroupOn
expression Required. An expression that returns on of the objects listed above.
| DscGroupOnEnum can be one of these DscGroupOnEnum constants. |
| dscDay |
| dscEachValue |
| dscHour |
| dscInterval |
| dscMinute |
| dscMonth |
| dscPrefix |
| dscQuarter |
| dscWeek |
| dscYear |
As it applies to the PivotField object.
This example groups the Age field of PivotTable1. Starting at age 15, a new group will be created for every five years until age 80. Then, the font, foreground, background, height and alignment of the resulting groups are formatted.
Sub AddGrouping()
Dim vwView
Dim ptConstants
Dim pfGroupedField
Set ptConstants = PivotTable1.Constants
' Set a variable to the active view of the PivotTable.
Set vwView = PivotTable1.ActiveView
' Set a variable to the Age field.
Set pfGroupedField = vwView.FieldSets("Age").Fields("Age")
' Set the GroupOn property so that the Age field will be
' grouped by the GroupInterval setting.
pfGroupedField.GroupOn = ptConstants.plGroupOnInterval
' Create a new grouping for every five years.
pfGroupedField.GroupInterval = 5
' Start the grouping at age 15.
pfGroupedField.GroupStart = 15
' End the grouping at age 80.
pfGroupedField.GroupEnd = 80
' Set the font for the field when it is grouped.
pfGroupedField.GroupedFont.Bold = True
' Set the foreground color for the field when it is grouped.
pfGroupedField.GroupedForeColor = "Black"
' Set the Background color for the field when it is grouped.
pfGroupedField.GroupedBackColor = "Blue"
' Set the height for the field when it is grouped.
pfGroupedField.GroupedHeight = 15
' Set the horizontal alignment for the field when it is grouped.
pfGroupedField.GroupedHAlignment = ptConstants.plHAlignRight
End Sub