NamedRange.Group Method

Article
01/07/2014

When the NamedRange control represents a single cell in a PivotTable field’s data range, the Group method performs numeric or date-based grouping in that field.

Namespace: Microsoft.Office.Tools.Excel
Assembly: Microsoft.Office.Tools.Excel (in Microsoft.Office.Tools.Excel.dll)

Syntax

'Declaration
Function Group ( _
    Start As Object, _
    End As Object, _
    By As Object, _
    Periods As Object _
) As Object

Object Group(
    Object Start,
    Object End,
    Object By,
    Object Periods
)

Parameters

Start
Type: System.Object

The first value to be grouped. If this argument is omitted or true, the first value in the field is used.

End
Type: System.Object

The last value to be grouped. If this argument is omitted or true, the last value in the field is used.

By
Type: System.Object

If the field is numeric, this argument specifies the size of each group. If the field is a date, this argument specifies the number of days in each group if element 4 in the Periods array is true and all the other elements are false. Otherwise, this argument is ignored. If this argument is omitted, Microsoft Office Excel automatically chooses a default group size.

Periods
Type: System.Object

An array of seven Boolean values that specify the period for the group, as shown:

1 - Seconds

2 - Minutes

3 - Hours

4 - Days

5 - Months

6 - Quarters

7 - Years

If an element in the array is true, a group is created for the corresponding time; if the element is false, no group is created. If the field is not a date field, this argument is ignored.

Return Value

Type: System.Object

Remarks

The NamedRange control must be a single cell in the PivotTable field’s data range. If you attempt to apply this method to more than one cell, it fails (without displaying an error message).

Optional Parameters

For information on optional parameters, see Optional Parameters in Office Solutions.

Examples

The following code example creates a PivotTable report and a NamedRange inside the area of the PivotTable report. It then uses the PivotTable, LocationInTable, PivotCell, PivotItem, and PivotField properties to display information about the placement of the NamedRange within the PivotTable report. The example also uses the Group method to perform numeric grouping based on the first value in the field.

This example is for a document-level customization.

Private Sub DisplayPivotTableInformation()
        ' Specify values for the PivotTable. 
        Me.Range("A1").Value2 = "Date" 
        Me.Range("A2").Value2 = "March 1" 
        Me.Range("A3").Value2 = "March 8" 
        Me.Range("A4").Value2 = "March 15" 

        Me.Range("B1").Value2 = "Customer" 
        Me.Range("B2").Value2 = "Smith" 
        Me.Range("B3").Value2 = "Jones" 
        Me.Range("B4").Value2 = "James" 

        Me.Range("C1").Value2 = "Sales" 
        Me.Range("C2").Value2 = "23" 
        Me.Range("C3").Value2 = "17" 
        Me.Range("C4").Value2 = "39" 

        ' Create and populate the PivotTable. 
        Dim table1 As Excel.PivotTable = _
            Me.PivotTableWizard( _
            Excel.XlPivotTableSourceType.xlDatabase, _
            Me.Range("A1", "C4"), Me.Range("A10"), "Sales Table", _
            False, False, True, False, , , False, False, _
            Excel.XlOrder.xlDownThenOver, , , )

        Dim customerField As Excel.PivotField = _
            CType(table1.PivotFields("Customer"), Excel.PivotField)
        customerField.Orientation = _
            Excel.XlPivotFieldOrientation.xlRowField
        customerField.Position = 1

        Dim dateField As Excel.PivotField = _
            CType(table1.PivotFields("Date"), Excel.PivotField)
        dateField.Orientation = _
            Excel.XlPivotFieldOrientation.xlColumnField
        dateField.Position = 1

        table1.AddDataField(table1.PivotFields("Sales"), _
            "Sales Summary", Excel.XlConsolidationFunction.xlSum)

        ' Create a NamedRange in the PivotTable and display the  
        ' location. 
        Dim namedRange1 As Microsoft.Office.Tools.Excel.NamedRange _
            = Me.Controls.AddNamedRange(Me.Range("B11"), _
            "namedRange1")
        namedRange1.Select()

        MessageBox.Show("The NamedRange is in the PivotTable report '" & _
            namedRange1.PivotTable.Name & "' at the location '" & _
            namedRange1.LocationInTable.ToString() & "'.")

        MessageBox.Show("The NamedRange has a PivotCell type of: " & _
            namedRange1.PivotCell.PivotCellType.ToString())

        MessageBox.Show("The NamedRange is in the PivotTable field: " & _
            namedRange1.PivotField.Name)

        MessageBox.Show("The NamedRange is in the PivotTable item: " & _
            namedRange1.PivotItem.Name)

        namedRange1.Group(True, , , )
    End Sub

private void DisplayPivotTableInformation()
{
    // Specify values for the PivotTable. 
    this.Range["A1"].Value2 = "Date";
    this.Range["A2"].Value2 = "March 1";
    this.Range["A3"].Value2 = "March 8";
    this.Range["A4"].Value2 = "March 15";

    this.Range["B1"].Value2 = "Customer";
    this.Range["B2"].Value2 = "Smith";
    this.Range["B3"].Value2 = "Jones";
    this.Range["B4"].Value2 = "James";

    this.Range["C1"].Value2 = "Sales";
    this.Range["C2"].Value2 = "23";
    this.Range["C3"].Value2 = "17";
    this.Range["C4"].Value2 = "39";

    // Create and populate the PivotTable.
    Excel.PivotTable table1 = this.PivotTableWizard(
        Excel.XlPivotTableSourceType.xlDatabase,
        this.Range["A1", "C4"],
        this.Range["A10"], "Sales Table", false,
        false, true, false, false, false,
        Excel.XlOrder.xlDownThenOver);

    Excel.PivotField customerField =
        (Excel.PivotField)table1.PivotFields("Customer");
    customerField.Orientation =
        Excel.XlPivotFieldOrientation.xlRowField;
    customerField.Position = 1;

    Excel.PivotField dateField =
        (Excel.PivotField)table1.PivotFields("Date");
    dateField.Orientation =
        Excel.XlPivotFieldOrientation.xlColumnField;
    dateField.Position = 1;

    table1.AddDataField(table1.PivotFields("Sales"),
        "Sales Summary", Excel.XlConsolidationFunction.xlSum);

    // Create a NamedRange in the PivotTable and display the  
    // location.
    Microsoft.Office.Tools.Excel.NamedRange namedRange1 =
        this.Controls.AddNamedRange(
        this.Range["B11"], "namedRange1");
    namedRange1.Select();

    MessageBox.Show("The NamedRange is in the PivotTable report '" +
        namedRange1.PivotTable.Name + "' at the location '" +
        namedRange1.LocationInTable.ToString() + "'.");

    MessageBox.Show("The NamedRange has a PivotCell type of: " +
         namedRange1.PivotCell.PivotCellType.ToString());

    MessageBox.Show("The NamedRange is in the PivotTable field: " +
         namedRange1.PivotField.Name);

    MessageBox.Show("The NamedRange is in the PivotTable item: " +
        namedRange1.PivotItem.Name);

    namedRange1.Group(true);
}

.NET Framework Security

Full trust for the immediate caller. This member cannot be used by partially trusted code. For more information, see Using Libraries from Partially Trusted Code.

Partager via