
		Forums Knowledge Base OfficeWriter Online


	This documentation is for OfficeWriter v3.7.1 ASP/COM Platform View Docs for Another Version or Platform

Programmer's Reference > ExcelApplication > Objects > Range

ExcelApplication is not
available in ExcelWriterSE.

The Range Object (SARange)

The Range Object allows you to make a modification to a range of cells within a worksheet, such as the application of a style, by the assignment of methods and properties. To create a Range object, use, Cells.Range, Cells.Rows, or Cells.Columns.

A Range object may include non-adjacent rectangular areas.

Range can be used to assign a certain group of cells to ExcelWriter's PrintArea property. PrintArea is a Range that is read by the PageSetup object when determining the portion of the page that should be printed.

Range Properties	Range Methods
Area AreaCount Cell ColumnCount ColumnWidth FirstColumn FirstRow Item Name RowCount RowHeight Style UsedRange Value	AddHyperlink AutoFitHeight AutoFitWidth BorderAround GetCellAt GroupColumns GroupRows Intersect MergeCells Union

Range Properties

Area

A Range may include non-adjacent rectangular areas. The Area property takes an index number, and returns - as a Range object - the corresponding rectangular area within the range.

Signature:


[VBScript]
Property Area(Index As Integer) As SARange (read-only)

Example:


[VBScript]

'--- "MyRange" is a SARange with 2 areas

'--- Get the first area from MyRange
Set rangeOne = MyRange.Area(1)

'--- Get the second area from MyRange
Set rangeTwo = MyRange.Area(2)

Top

AreaCount

A Range may include non-adjacent rectangular areas. AreaCount returns the number of rectangular areas in a Range object.

Signature:


[VBScript]
Property AreaCount As Integer (read-only)

Example:


[VBScript]

//--- "MyRange" is a SARange object

//--- Get the number of areas in MyRange
NumAreas = MyRange.AreaCount

Top

Cell

Returns the value of a specified cell in the range. Cell takes one argument, the cell's name (e.g. "a1").

Signature:


[VBScript]
Property Cell(CellName As String) As SACell (read-only)

Example:

[VBScript]

Dim XlwApp, WrkSht, MyRange, FirstCell
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
SAWorksheet WrkSht = XlwApp.Worksheets[1];

'--- This is the range A2:B4
Set MyRange = WrkSht.Cells.Range(2,1,2,2)

'--- Get the first cell in the range
Set FirstCell = MyRange.Cell("A1")

Important: Range.cell's reference is relative to the first cell in the range, not the first cell in the worksheet. For example, if the range spans the cells b2:d4, range.cell("a1") will return the value contained in worksheet.cells("b2").

ColumnCount

Returns the number of columns in the range.

Signature:


[VBScript]
Property ColumnCount As Integer (read-only)

Example:

[VBScript]

Dim XlwApp, WrkSht, MyRange, ColCount
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
SAWorksheet WrkSht = XlwApp.Worksheets[1];

'--- This is the range A2:B4
Set MyRange = WrkSht.Cells.Range(2,1,2,2)

'--- Get the number of columns in the range
ColCount = MyRange.ColumnCount ' returns 2

Top

ColumnWidth

Use ColumnWidth to set the width of columns in a specified range.

Signature:


[VBScript]
Property ColumnWidth As Double (write-only)

Example:

[VBScript]

Dim XlwApp, WrkSht, MyRange
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
SAWorksheet WrkSht = XlwApp.Worksheets[1];

'--- This range has columns A through D
Set MyRange = WrkSht.Cells.Columns(1,4)

'--- Set the column width for the range to 10
MyRange.ColumnWidth = 10

Top

FirstColumn

Returns the number of the first column in the range.

Signature:


[VBScript]
Property FirstColumn As Integer (read-only)

Example:

[VBScript]

Dim XlwApp, WrkSht, MyRange, FirstCol
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
SAWorksheet WrkSht = XlwApp.Worksheets[1];

'--- This range has columns D through F
Set MyRange = WrkSht.Cells.Columns(4,3)

'--- Get the first column in the range
FirstCol = MyRange.FirstColumn ' returns 4

Top

FirstRow

Returns the number of the first row in the range.

Signature:


[VBScript]
Property FirstRow As Integer (read-only)

Example:

[VBScript]

Dim XlwApp, WrkSht, MyRange, FirstRow
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
SAWorksheet WrkSht = XlwApp.Worksheets[1];

'--- This range has Rows 4 through 6
Set MyRange = WrkSht.Cells.Rows(4,3)

'--- Get the first column in the range
FirstRow = MyRange.FirstRow ' returns 4

Top

Item

Returns the value of a specified cell in the range. Item takes either the cell name (e.g. "a1"), or the cell row and column (e.g. (1,1)). Item is the default property of the Range object, so range(1,1) is equivalent to range.item(1,1).

Signature:


[VBScript]
In VBScript, this is the default property for the SARange class.

Property Item(Row As Variant[, Column As Long]) As SACell (read-only)

Example:


[VBScript]

Dim XlwApp, WrkSht, MyRange, FirstCell
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
SAWorksheet WrkSht = XlwApp.Worksheets[1];

'--- This range is B4:E9
Set MyRange = WrkSht.Cells.Range(4,2,6,4)

'--- Get the first cell in the range
Set FirstCell = MyRange(1,1) ' MyRange("A1") is also valid in VBScript

Important: Item's cell reference is relative to the first cell in the range, not the first cell in the worksheet. For example, if the range spans the cells b2:d4, range.item("a1") will return the value contained in worksheet.cells("b2"). Top

Name

Sets or returns the name of a specified range of cells. In an Excel spreadsheet, named ranges are listed in the name box (above the top left corner of the spreadsheet); to move to a named range, select the name from the name list.

A range name must not include spaces.

Signature:


[VBScript]
Property Name As String (read/write)

Example:


[VBScript]

WrkSht.Cells("A1").Value = "Last Name"
WrkSht.Cells("B1").Value = "First Name"
WrkSht.Cells("C1").Value = "Address"
WrkSht.Cells("D1").Value = "Phone"
	
'--- Create a range that contains cells A1:D1
Set HeadingRange = WrkSht.Cells.Range(1,1,1,4)

'--- Create a name for the range
HeadingRange.Name = "Headings"

When assigning a range name, do not use any of the following built-in names:

Consolidate_Area	Print_Titles
Auto_Open	Recorder
Auto_Close	Data_Form
Extract	Auto_Activate
Database	Auto_Deactivate
Criteria	Sheet_Title
Print_Area

RowCount

Returns the number of rows in the range.

Signature:


[VBScript]
Property RowCount As Integer (read-only)

Example:

[VBScript]

Dim XlwApp, WrkSht, MyRange, NumRows
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
SAWorksheet WrkSht = XlwApp.Worksheets[1];

'--- This range is B4:E9
Set MyRange = WrkSht.Cells.Range(4,2,6,4)

'--- Get the number of rows in the range
NumRows = MyRange.RowCount ' returns 6

RowHeight

Use RowHeight to set the height of rows in a specified range.

Signature:


[VBScript]
Property RowHeight As Integer (write-only)

Example:


[VBScript]

Dim XlwApp, WrkSht, MyRange
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)

'--- This range is rows 2 through 6
Set MyRange = WrkSht.Cells.Rows(2,5)

'--- Set the row height to 10
MyRange.RowHeight = 10

Style

Setting this property allows for the easy application of a pre-defined custom or intrinsic ExcelWriter style to each and every cell that is a member of the range to which the style assignment is made.

Signature:


[VBScript]
Property Style As SAStyle (write-only)

If a cell is part of more than one range, and each range has a different style, the final applied style will overwrite all previously assigned styles. To overwrite the style for a single cell, use the Cell.Style property. To supplement the previously applied style for a single cell with additional formatting properties, use the Cell.Format property.

Example: The following example creates a custom “TextStyle” with property settings. It defines a range object (Rng1) that encompasses two rows and spans 5 columns and then assigns the custom “TextStyle” to this range. Finally, it adds an additional formatting property (bold faced font) to Cell “E5” only (which is a member of Rng1).

 
[VBScript]

Set TextStyle = xlw.CreateStyle
TextStyle.Number = 49 
TextStyle.Font = MyFont
TextStyle.WrapText = True
set Rng1 = Ws.Cells.Range(3,3,2,5)
Rng1.Style = TextStyle

Ws.Cells(3,5).Format.Font.Bold = True

Important: It is essential, when designing the layout of a workbook, to understand the ramifications of using multiple styles and formats and to carefully pre-plan a schema for applying them. For more information on applying styles and formats correctly, see Adding Formatting.

UsedRange

Signature:


[VBScript]
Property UsedRange As SARange (read-only)

Example:


[VBScript]

Dim XlwApp, WrkSht, MyRange, UsedRng
Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set WrkSht = XlwApp.Worksheets(1)

'--- This range is A1:B6
Set MyRange = WrkSht.Cells.Range(1,1,6,2)

'--- Get the range of used cells
Set UsedRng = MyRange.UsedRange

Description

UsedRange returns a range of used cells within the specified range. If there are no use cells in the range, an empty range is returned.

UsedRange is a Read Only property.

Value

Sets or returns, as a two-dimensional array, the cell values contained in a Range object.

Signature:


[VBScript]
Property Value As Variant (read/write)

Example: The following example creates column headings for the first worksheet in a workbook, and uses the Range object's Value property to assign identical headings to the second worksheet.

 
[VBScript]

set ws1 = xlw.worksheets(1)
set RngColHeadings1 = ws1.cells.range(1,1,1,4) 
ws1.cells("A1").value = "Last Name"
ws1.cells("B1").value = "First Name"
ws1.cells("C1").value = "Address"
ws1.cells("D1").value = "Phone"
	
set ws2 = xlw.worksheets(2)
set RngColHeadings2 = ws2.cells.range(1,1,1,4)
RngColHeadings2.value = RngColHeadings1.value

Range Methods

AddHyperlink

Inserts a hyperlink into a range of cells.

Signature:


[VBScript]
Sub AddHyperLink(Address As String[, SubAddress As String])

To insert a hyperlink in a range,

Merge the range of cells that will contain the link. This is not required. However, note that all cells
in the range will function as a single link. Merging the cells in the range makes it clear that the range
is a single unit.
Use AddHyperlink to insert a link in the range. AddHyperlink assigns an address, and,
optionally, a sub-address (e.g. "/search.htm"). The address may be http, https, ftp, mailto, or a UNC
path.
Assign a value to a cell in the range. The cell value will function as the link.
Apply the hyperlink style to the cell containing the link.

Note: Excel 95 does not support this feature.

Example:

 

[VBScript]

Set XlwApp = Server.CreateObject("SoftArtisans.ExcelWriter")
Set Cells = XlwApp.Worksheets(1).Cells

Set Rng = Cells.Range(15,1,2,2)
Rng.MergeCells
Rng.AddHyperlink "http://www.softartisans.com"
Cells(15,1).value = "Click here for the SoftArtisans Home Page"
Cells(15,1).Style = XlwApp.Style("Hyperlink")

AutoFitHeight

When WrapText is set to True, AutoFitHeight will adjust the row height of all rows in the range, according to the column width.

Signature:


[VBScript]
Sub AutofitHeight()

Note: To ensure that AutoFitHeight is applied correctly, set the values of the range before setting AutoFitHeight.

Example:

[VBScript]

range.ColumnWidth = 5
Set mystyle = xlw.CreateStyle
mystyle.WrapText = True
range.Style = mystyle
range.AutoFitHeight

AutoFitWidth

Adjusts the width of all columns in the range to the width of their longest values.

Signature:


[VBScript]
Sub AutofitWidth()

Note: To ensure that AutoFitWidth is applied correctly, set the values of the range before setting AutoFitWidth.

Example:

 
[VBScript]

range.ColumnWidth = 5
Set mystyle = xlw.CreateStyle
mystyle.WrapText = True
range.Style = mystyle
range.AutoFitWidth

BorderAround

Syntax

Signature:


[VBScript]
Sub BorderAround(BorderLineStyle As SABorderLineStyle, BorderColor As Long)

Description

BorderAround creates a border around the specified range.

Parameters

BorderAround takes two parameters: BorderLineStyle and BorderColor. Set BorderLineStyle to an SABorderLineStyle value by name or number:

SABorderLineStyle Values
`sadgNone`	`0`
`sadgThin`	`1`
`sadgMedium`	`2`
`sadgDashed`	`3`
`sadgDotted`	`4`
`sadgThick`	`5`
`sadgDouble`	`6`
`sadgHair`	`7`
`sadgMediumDashed`	`8`
`sadgDashDot`	`9`
`sadgMediumDashDot`	`10`
`sadgDashDotDot`	`11`
`sadgMediumDashDotDot`	`12`
`sadgSlantedDashDot`	`13`

Example

 

[VBScript]

'--- This range is A1:B6
Set MyRange = WrkSht.Cells.Range(1,1,6,2)

'--- Apply a thin blue border around cells A1:B6
MyRange.BorderAround sadgThin, RGB(0,0,255)

GetCellAt

Gets the value contained in a specified cell. GetCellAt takes two arguments, row and column.

Signature:


[VBScript]
Function GetCellAt(Row As Integer, Column As Integer) As SACell

Important: GetCellAt's parameters are relative to the first cell in the range, not the first cell in the worksheet. For example, if the range spans the cells b2:d4, range.GetCellAt(2,2) will return the value contained in worksheet.cells("c3").

Example:


[VBScript]

Set FirstRangeCell = range.GetCellAt(1,1)

GroupColumns

Outlines specified columns within a range.

Signature:


[VBScript]
Sub GroupColumns(FirstColumn As Long, _
[NumColumns As Long = 1], _
[Collapsed As Boolean = False])

GroupColumns takes three parameters:

FirstColumn

First column to be included in the outline. The first column can be designated as a summary column, (see the Cells.SummaryColumn property) and, if so, should be visible. Columns that follow or precede the summary column contain the outline detail and may be visible or hidden, depending on the value of the collapsed parameter. Column numbering is 1-based.

NumColumns

Optional.
Default value: 1.
Column numbering is 1-based.

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.

Important: If you wish to designate the location of the summary column as the last column of the outlined group, then the Cells.SummaryColumn property must be set using the saxlSummaryOnRight (0) value. The summary column should always be visible.

Example:

 
[VBScript]

'--- Define a range from cell A2 to cell E11
Set range1 = ws.Cells.Range(2,1,10,5)

'--- Outline columns A to D, and hides the outline detail columns.
'--- Column A is the summary column and will be visible. This 
'--- assumes that you have set the "SummaryColumn" property to 
'--- place summary columns to the left of detail columns. Columns 
'--- B to D are the outline detail columns and will be hidden.
range1.GroupColumns 1, 4, True

See also, GroupRows.

GroupRows

Outlines specified rows within a set of cells.

Signature:


[VBScript]
Sub GroupRows(FirstRow As Long, [NumRows As Long = 1],_
	[Collapsed As Boolean = False])

GroupRows takes three parameters:

FirstRow

First row to be included in the outline. The first row can be designated as a summary row, (see the Cells.SummaryRow property) and, if so, should be visible. Rows that follow or precede the summary row contain the outline detail and may be visible or hidden, depending on the value of the collapsed parameter. Row numbering is 1-based.

NumRows

Optional.
Default value: 1.
Row numbering is 1-based.

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.

Important: If you wish to designate the location of the summary row as the last row of the outlined group, then the Cells.SummaryRow property must be set using the saxlSummaryBelow (0) value. The summary row should always be visible.

Example:

 
[VBScript]

'--- Define a range from cell A2 to cell E11
Set range1 = ws.Cells.Range(2,1,10,5)

'--- Outline rows 2 to 4, and hide the outline detail rows.
'--- Row 2 is the summary row and will be visible. This assumes 
'--- that you have set the "SummaryRow" property to place the 
'--- summary row above the detail rows. Rows 3 and 4 are the 
'--- outline detail rows and will be hidden.
range1.GroupRows 1, 3, True

See also, GroupColumns.

Intersect

Creates an intersection of two Range objects.

Signature:


[VBScript]
Sub Intersect(Range As SARange)

The Intersect method takes one parameter, a Range object.

Example:

 

[VBScript]

set range3 = ws1.cells.range(22,7,5,4)
set range4 = ws1.cells.range(23,5,5,4)
range3.intersect range4

MergeCells

Combines a range of specified cells into a single cell. Use the address of the upper-left cell in the range to reference the merged cell.

Note: Excel 95 does not support this feature.

Signature:


[VBScript]
Sub MergeCells()

Example:

 

[VBScript]

'--- Create a range and merge the cells into one
Set Rng = Cells.Range(15,1,2,2)
Rng.MergeCells

Union

Creates a union of two Range objects.

Signature:


[VBScript]
Sub Union(range As SARange)

The Union method takes one parameter, a Range object.

Example:


[VBScript]

'--- Create a union of two different ranges
Set range4 = ws1.Cells.Range(16,1,5,4)
Set range5 = ws1.Cells.Range(23,5,5,4)
range4.Union range5

Copyright 2006 © SoftArtisans, Inc. All Rights Reserved.