Page 209 - Excel Progamming Weekend Crash Course

k540629 ch14.qxd 9/2/03 9:34 AM Page 184

184 Saturday Afternoon

Changing Row and Column Size

The width of columns and the height of rows in a worksheet can be changed to accommo-
date the data they contain. A column’s width is measured in terms of characters — specifi-
cally, it is a multiple of the width of the 0 (zero) character in the default font. The default
column width is approximately 8.5. Column width is controlled by the ColumnWidth property.
One way to set column width is used when you know the column(s) that needs to be
changed. Use the Worksheet object’s Columns collection to reference the columns. For
example, this code sets the width of column B in the active worksheet to 16:

ActiveSheet.Columns(“B”) = 16
The following code doubles the width of columns C through E on Sheet1:

With Worksheets(“Sheet1”).Columns(“C:E”)
.ColumnWidth = .ColumnWidth * 2
End With

The other way to set column width is to set the width of the columns in a range using
the EntireColumn property. This lets you set column width without knowing the identity
(letters) of the columns in advance. This code, for example, sets the width of columns in the
current selection to 15:
Selection.EntireColumn.ColumnWidth = 15

One very useful technique is to set the width of columns as required by the data in the
columns. The AutoFit method does this, setting the column to the width required to dis-
play the widest cell data in the column.
AutoFit works with data currently in the row. If you later add data that is
wider, the column does not automatically resize to fit the new data — you
Tip must call AutoFit again.
This code uses AutoFit to set the size of columns A:E in the active worksheet:

ActiveSheet.Columns(“A:E”).AutoFit

This code sets the width of the columns spanned by the specified range:
SomeRange.EntireColumn.AutoFit
Row height is measured in points; there are 72 points in an inch. Use of this measure-
ment unit makes sense when you recall that font height is also measured in points, and
that row height is usually adjusted to fit the fonts used. Excel automatically increases row
height as needed to fit the largest font in the row, but you still may want to change the row
height in your program for special formatting needs.
There are two Range properties that apply to row height. Height is read-only and is used
to obtain information about row height. RowHeight is read/write and is used to set row
height as well as to obtain information about row height. The information that is returned
from these properties differs as described in Table 14-6.