For programmatically constructing a consolidation table, the Consolidate method of the Range object is used. This method allows you to summarize and consolidate homogeneous data placed in several ranges. On a worksheet, the actions programmed by the Consolidate method correspond to the Consolidate command located in the Data Tools group on the Data tab of the ribbon.
expression.Consolidate(Sources, Function, TopRow, LeftColumn, CreateLinks)
- expression — a reference to the range or cell in its upper-left corner where the consolidation table will be created.
- Sources — optional parameter, specifies an array of references in R1C1 format to the ranges from which the consolidation table is built. The references must contain full range names including worksheet names. Example:
Array("'January'!R1C1:R5C3", "'February'!R1C1:R5C3")
- Function — optional parameter, specifies the function on which the consolidation table is based. Acceptable values are the following XlConsolidationFunction constants:
- xlAverage (average),
- xlCount (number of values),
- xlCountNums (number of numbers),
- xlMax (maximum),
- xlMin (minimum),
- xlProduct (product),
- xlStDev (unbiased variance),
- xlStDevP (biased variance),
- xlSum (sum),
- xlVar (unbiased deviation),
- xlVarP (biased deviation).
- TopRow — optional Boolean parameter. Indicates whether consolidation is based on the column headers of the consolidated ranges.
- LeftColumn — optional Boolean parameter. Indicates whether consolidation is based on the row headers of the consolidated ranges.
- CreateLinks — optional Boolean parameter. Indicates whether the consolidated table is linked to the source tables. If set to True, the consolidated table is displayed as an outline.
Properties of the Worksheet Object Important for Data Consolidation
In data consolidation, three properties of the Worksheet object play an important role.
Table. Properties of the Worksheet object used in data consolidation
| Property | Description |
| ConsolidationOptions | Returns a three-element array. The first element indicates whether the consolidation is based on column headers. The second element indicates whether it is based on row headers of the consolidated ranges. The third element indicates whether the consolidated table is linked to the source tables. |
| ConsolidationFunction | Returns an XlConsolidationFunction constant that identifies the function on which the consolidation table is built. |
| ConsolidationSources | Returns an array of references to the ranges on which the consolidation table on the worksheet was built. If there is no such table on the worksheet, this property returns the value Empty. |