Defining Ranges

fixi Ta | You can also use the Width property to determine the width of a particular column. Be aware that the Width property returns the measurement of the column width in points, unlike the ColumnWidth property, which returns characters. You typically use a point to reference font sizes, with 1 point equivalent to % of an inch. For example, the default font size that Excel uses is typically 10 point.

The Width property is read-only, meaning that you can only use it to return the width of a column, and not to modify the width. You can return the Width property of a column by assigning the value to a variable, as shown in the following code:

Example:

ColWidth = Column(4).Width

Because the only method for changing the width of the column is the ColumnWidth property, which uses the number of characters that fit in a column and not a point value, you may not use the Width property as frequently as the ColumnWidth property. The Width property is valuable if you need to compare the column width to the row height, because Excel does store the row height as a point value.

-■ Excel resizes the columns in the specified range to the new width.

Note: See Chapter 6 for more information on using the For Next looping statement.

□ Type NewRange.Columns (I).ColumnWidth = 15, replacing 15 with the desired column width.

-■ Excel resizes the columns in the specified range to the new width.

ou can modify the height of rows within a range using the RowHeight property. When you use this property, you specify how high the specific row displays when you view the worksheet containing the row in Excel. By default, Excel assigns a height of 12.75 points to each row. A point is a measurement Excel uses with font sizes, with each point being approximately >72 of an inch. You measure a font based upon the height of a capital character, such as W, to determine the point size. Because the default font size in Excel is 10 point, typically the default row size of 12.75 points is adequate for displaying text in cells. Of course, if you specify a larger font size, or if you want the text to wrap within the cell, you need to specify a larger row size using the RowHeight property.

You can set the height of the row by assigning a numeric value to the RowHeight property for the corresponding object. For example, to change the height of row 2 to 25, you use the code: Rows(2).RowHeight=25. Keep in mind, when you use the Rows property without referencing the corresponding Range object, Excel automatically uses the current active sheet.

If the row height you specify is not high enough to display the entire size of the font, the text appears cut off in the row when you view it in Excel.

You can use the RowHeight property to determine the height of the rows in a range. If all rows in the range have the same height, the height is returned as the number of points. If the rows within the selected range do not have the same height, a value of Null is returned.

SET THE HEIGHT OF ROWS IN A RANGE

SET THE HEIGHT OF ROWS IN A RANGE

□ Create a new subroutine.

Type Dim NumRows As Type Set NewRange

NewRange. Rows.Count.

□ Create a new subroutine.

Type Dim NumRows As Type Set NewRange

Type Dim NewRange As Range, replacing NewRange with the name of the range of cells.

Integer, replacing NumRows with the name of the variable containing the number of rows.

Type Dim AverageHeight As Long, replacing AverageHeight with the variable to contain average row height.

Range("A1:CI4"), replacing Range("A1:C14") with the range of cells.

NewRange. Rows.Count.

'-Q Type AverageHeight = NewRange.Height / NumRows.

0 0

Post a comment