Inserts a hyperlink into a range of cells. AddHyperlink takes two arguments:

AddHyperlink(Address, [SubAddress])

To insert a hyperlink in a range,

1. 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.
   
   
2. 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.
   
   
3. Assign a value to a cell in the range. The cell value will function as the link.
   
   
4. Apply the hyperlink style to the cell containing the link.

Note: Excel 95 does not support this feature.

Example:

 
Set Rng = Cells.Range(15,1,2,2)
Rng.MergeCells
Rng.AddHyperlink "http://www.yahoo.com"
Cells(15,1).value = "Yahoo!"
Cells(15,1).Style = HyperLink
		

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.

Example: The following lines assign names to areas within a range.

range4.area(1).name = "rng4_area1" 
range4.area(2).name = "rng4_area2"
range4.area(3).name = "rng4_area3"
		

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

 
NumRangeAreas = Range.AreaCount
		

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

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

Example:

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

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

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

Example:

 
range.AutoFitWidth
		

Returns the value of a specified cell in the range. Cell takes one argument, the cell's name (e.g. "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").

Example:

 
FirstCell = range.cell("a1")
		

Returns the number of columns in the range.

Example:

 
ColumnsInRange = range.columncount
		

Use ColumnWidth to set the width of rows in a specified range. The following example demonstrates defining a range from row 4 to row 8, and setting ColumnWidth to 10.

Set Range = Application.Wks(1).Cells.Columns(4, 5)
Range.ColumnWidth = 10
		

Top

Returns the number of the first column in the range.

If the range starts at column c, the following will assign the number 3 to FirstColInRange:

 
FirstColInRange = range.firstcolumn
		

Returns the number of the first row in the range.

If the range starts at row 2, the following will assign the number 2 to FirstRowInRange:

 
FirstRowInRange = range.firstrow
		

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

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:

FirstRangeValue = range.GetCellAt(1,1) 
		

Outlines specified columns within a range. GroupColumns takes three parameters:

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:

 
'--- 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.

Top

Outlines specified rows within a set of cells. GroupRows takes three parameters:

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:

 
'--- 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.

Top

Creates an intersection of two Range objects. The Intersect method takes one parameter, a Range object.

Example:

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

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).

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").

Specify a range item by cell name, or cell row and column, with or without the property name:

 
'--- By row and column
FirstItem = range.item(1,1)
		
'--- By name
FirstItem = range.item("a1")

'--- As default property
FirstItem = range(1,1)
		

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.

Example:

 
set Rng = Cells.Range(15,1,2,2)
Rng.MergeCells
		

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.

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

Example:

 
ws.cells("A1").value = "Last Name"
ws.cells("B1").value = "First Name"
ws.cells("C1").value = "Address"
ws.cells("D1").value = "Phone"
	
set range1 = ws.cells.range(1,1,1,4)
range1.name = "Headings"
		

Returns the number of rows in the range.

Example:

 
RowsInRange = range.rowcount
		

Use RowHeight to set the height of rows in a specified range. The following example demonstrates defining a range from row 4 to row 8, and setting RowHeight to 10.

 
Set Range = Application.Wks(1).Cells.Rows(4, 5)
Range.RowHeight = 10
		

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.

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).

 
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.

Top

Creates a union of two Range objects. The Union method takes one parameter, a Range object.

Example:

set range4 = ws1.cells.range(16,1,5,4)
set range5 = ws1.cells.range(23,5,5,4)
range4.union range5
		

Top

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

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.

 
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
		

Top