Microsoft Office Tutorials and References

In Depth Information

**19.3.14 Consolidate Method**

Columns("A").ColumnWidth = 2

MsgBox Columns("A").Width ' Displays 15

Columns("A").ColumnWidth = 10

MsgBox Columns("A").Width ' Displays 58.2

Fortunately, a little high-school algebra reveals the truth here. It appears that the Width property

includes padding on the far right and the far left of the entire group of characters (next to the

column boundaries). To support this conclusion, let's do a little algebra, which you can skip if it

upsets you.

Assume for a moment that the Width property includes not just the sum of the widths of the

ColumnWidth characters, but also an additional
p
points of padding on each side of the entire

group of characters. Thus, the formula for Width is:

Width = 2*p + ColumnWidth*w

where
w
is the true width of a single "0" character, in points. Thus, plugging in the values from the

first two examples in the previous code gives:

9.6 = 2*p + 1*w

15 = 2*p + 2*w

Subtracting the first equation from the second gives:

5.4 = w

Substituting this into the first equation and solving for
p
gives:

p = 2.1

Thus, the formula for a Normal style of 10 point Arial is:

Width = 4.2 + ColumnWidth*5.4

Now, for a ColumnWidth of 10, this gives:

Width = 4.2 + 10*5.4 = 58.2

Eureka! (Check the third example in the previous code.)

Thus, we have verified (but not really proved) that the Width property measures not just the width

of each character but includes some padding on the sides of the columnâ€”in this case 2.1 points of

padding on each side.

19.3.14 Consolidate Method

This method combines (or consolidates) data from multiple ranges (perhaps on multiple

worksheets) into a single range on a single worksheet. Its syntax is:

RangeObject
.Consolidate(
Sources
,
Function
, _

TopRow
,
LeftColumn
,
CreateLinks
)

Sources
is the source of the consolidation. It must be an array of references in R1C1-style

notation. The references must include the full path of the ranges to be consolidated. (See the

following example.)