The Range Object |
 |
What is a Range object?
The Range object represents a range of cells within a worksheet. A Range object may include non-adjacent areas (with ExcelWriter 3 or higher). To create a Range object, use, Cells.Range, Cells.Rows, or Cells.Columns.
The property Cells.Range retrieves specified cells into a range object.
By default, Cells.Range retrieves all cells in a
Cells object. To specify a range of cells
within the Cells object, set the Range property's four optional parameters.
For example, to set a Range of from row 3 to row 4, and column 10 to column 14, use,
set Rng = Cells.Range(3,10,2,5)
| Range.Cells Optional Parameter | Default Value | |
|---|---|---|
| FirstRow | 1 | |
| FirstCol | 1 | |
| NumRows | 65536 | ExcelWriter Limit imposed by Microsoft's BIFF8 format |
| 500 | ExcelWriterFree Edition Limit | |
| NumCols | 256 | Limit imposed by Microsoft's BIFF8 format, not by ExcelWriter |
- Example 1: Outlines
- Example 2: Union, Intersection, Names, and Outlines
- Example 3: Using Range to Set PrintArea
- Example 4: Using Range to Display Text
- Example 5: Applying a Border to a Range of Cells
- Example 6: Assigning Values from an Array with Range.Value
- Example 7: Getting Values from a Worksheet with Range.Value
- Example 7: Applying a Style to a Range of Cells
Range properties and methods
| Range Properties | Range Methods |
|---|---|
| *This feature is not available in ExcelWriterSE, ExcelWriterLE, or ExcelWriter Free. | |
Example 1: Outlines
Range2.asp uses GroupColumns* and GroupRows* to create outlines within a range.
The first row or column in an outline is the summary row or column, and is always
visible. The rows or columns following
the summary are the outline detail rows or columns, and will be visible or hidden,
depending on the value of the Collapsed parameter. For definitions of
GroupColumns/Rows parameters, see the following table.
| GroupColumns | GroupRows | ||
|---|---|---|---|
FirstColumn |
First column included in the outline. The first column is a summary column,
and is always visible. Columns following the summary column contain the outline
detail and may be visible or hidden, depending on the value of the
Assign to |
FirstRow |
First row included in the outline. The first row is a summary row,
and is always visible. Rows following the summary row contain the outline
detail and may be visible or hidden, depending on the value of the
Assign to |
NumColumns | Optional.
Default value: 1.
Column numbering is
1-based. NumColumns
does not include the summary
column. |
NumRows | Optional.
Default value: 1.
Row numbering is
1-based. NumRows
does not include the summary
row. |
Collapsed | Optional. Default value: False. If set to True, the outline detail columns will be hidden. If set to False, the outline detail columns will be visible. Note: The summary column - the first column included in the outline - is always visible. | Collapsed | Optional. Default value: False. If set to True, the outline detail rows will be hidden. If set to False, the outline detail rows will be visible. Note: The summary row - the first row included in the outline - is always visible. |
*This feature is not available in ExcelWriterSE, ExcelWriterLE, or ExcelWriter Free.
Example 2: Union, Intersection, Names, and Outlines
The following example imports data from the Northwind database to an Excel spreadsheet. The script sets five ranges, and names each using the Name* property. The range names will be listed in Excel's drop-down name box above the top left corner of the spreadsheet. Select a range name from the list to highlight a range.
Range1 is defined as the set of cells beginning at row 4, column 2, and spanning 4 rows and 5 columns:
Set Range1 = ws1.cells.range(4,2,4,5)
Later Range1 is redefined using the Union* method, which creates a union of the two ranges Range1 and Range2:
Range1.union(Range2)
Range2 - originally the set of cells from cell (6,3) to cell (12,11) - is redefined by the Intersect* method as the intersection of Range2 and Range3:
Range2.intersect(Range3)
GroupColumns*
and GroupRows*
are used to create outlines within Range4 and Range5. The first row or column in an
outline is the summary row or column, and is always visible. The rows or columns following
the summary are the outline detail rows or columns, and will be visible or hidden,
depending on the value of the Collapsed parameter. For definitions of
GroupColumns/Rows parameters, see the table below. The following line
outlines columns 13 to 20, and hides the outline detail columns.
Range4.GroupColumns 13,7,True
*This feature is not available in ExcelWriterSE, ExcelWriterLE, or ExcelWriter Free.
Example 3: Use Range to Set PrintArea*
Range can be used to assign a certain group of cells to ExcelWriter's PrintArea* property. PrintArea* is simply a Range that is read by the PageSetup* object when determining the portion of the page that should be printed.
A Range can be directly assigned to a PrintArea*:
Ws.PageSetup.PrintArea = Ws.Cells.Range(1, 1, 20, 10)
Or it can be assigned to a variable which can be used to set the PrintArea*:
Set pa = Ws.Cells.Range(1, 1, 20, 10) Ws.PageSetup.PrintArea = pa
*This feature is not available in ExcelWriterSE, ExcelWriterLE, or ExcelWriter Free.
Example 4: Using Range to Display Text
The following sample uses MergeCells* to display a large block of text within a range of cells.
*This feature is not available in ExcelWriterSE, ExcelWriterLE, and ExcelWriterFree.
Example 5: Applying a Border to a Range of Cells
BorderAround.asp creates a function called ApplyBorderToRange. The
function takes an ExcelApplication
object, a Range object, and a
BorderLineStyle, and applies
a border to the specified range of cells.
Example 6: Assigning Values from an Array with Range.Value*
RangeValue1.asp assigns values to a block of cells from a 2-dimentional array using the
Range.Value property.
*This feature is not available in ExcelWriterSE, ExcelWriterLE, and ExcelWriterFree.
Example 7: Getting Values from a Worksheet with Range.Value*
RangeValue2.asp gets values from a worksheet and populates a 2D array using
the Range.Value property.
*This feature is not available in ExcelWriterSE, ExcelWriterLE, and ExcelWriterFree.
Example 8: Applying a Style to a Range of Cells
This samples demonstrates how to use Range.Style* to apply a style to a range of cells, and then use Cell.Format refine the style of specific cells.
*This feature is not available in ExcelWriterSE, ExcelWriterLE, and ExcelWriterFree.
Copyright © 2003, SoftArtisans, Inc.