NamedRange.Consolidate Method

Article
01/07/2014

Consolidates data from multiple ranges on multiple worksheets into the NamedRange control.

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

Syntax

'Declaration
Function Consolidate ( _
    Sources As Object, _
    Function As Object, _
    TopRow As Object, _
    LeftColumn As Object, _
    CreateLinks As Object _
) As Object

Object Consolidate(
    Object Sources,
    Object Function,
    Object TopRow,
    Object LeftColumn,
    Object CreateLinks
)

Parameters

Sources
Type: System.Object

The sources of the consolidation as an array of text reference strings in R1C1-style notation. The references must include the full path of sheets to be consolidated.

Function
Type: System.Object

Can be one of the following XlConsolidationFunction constants:

xlAverage

xlCount

xlCountNums

xlMax

xlMin

xlProduct

xlStDev

xlStDevP

xlSum

xlVar

xlVarP

TopRow
Type: System.Object

true to consolidate data based on column titles in the top row of the consolidation ranges; false to consolidate data by position. The default value is false.

LeftColumn
Type: System.Object

true to consolidate data based on row titles in the left column of the consolidation ranges; false to consolidate data by position. The default value is false.

CreateLinks
Type: System.Object

true to have the consolidation use worksheet links; false to have the consolidation copy the data. The default value is false.

Return Value

Type: System.Object

Remarks

Optional Parameters

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

Examples

The following code example sets the range of cells from B1 through D10 to random numbers and then uses the Consolidate method to consolidate this range into a NamedRange control.

This example is for a document-level customization.

Private Sub SetConsolidation()
        Dim namedRange1 As Microsoft.Office.Tools.Excel.NamedRange _
            = Me.Controls.AddNamedRange(Me.Range("A1"), _
            "namedRange1")

        Dim Range1 As Excel.Range = Me.Range("B1", "D10")
        Range1.Formula = "=rand()" 

        Dim [source]() As String = {"Sheet1!R1C2:R10C4"}
        namedRange1.Consolidate([source], _
        Excel.XlConsolidationFunction.xlSum, False, False, False)

    End Sub

private void SetConsolidation()
{
    Microsoft.Office.Tools.Excel.NamedRange namedRange1 =
        this.Controls.AddNamedRange(this.Range["A1"],
        "namedRange1");

    Excel.Range Range1 = this.Range["B1", "D10"];
    Range1.Formula = "=rand()";

    string[] source = new string[] { "Sheet1!R1C2:R10C4" };
    namedRange1.Consolidate(source, Excel.XlConsolidationFunction.xlSum,
        false, false, false);
}

.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.