Excel VBA - Range

About

Range represents:

• a cell,
• a row, a column,
• a selection|selection of cells containing one or more contiguous blocks of cells,
• or a 3-D range.

Articles Related

Range

Range property:

Address

One Cell

If you use a text argument for the range address, you must specify the address in A1-style notation (you cannot use R1C1-style notation).

• Fully Qualified

Worksheets("Sheet1").Range("A5").Value

• Active Worksheet

Worksheets("Sheet1").Activate
Range("A5").Value

• Range.Cells(row, column) where row and column are relative to the upper-left corner of the range

Worksheets(1).Range("C5:C10").Cells(1, 1).Formula = "=Rand()"

Range

A range is one table on one sheet

• Fills the range A1:H8 with random numbers by setting the formula for each cell in the range.

Worksheets("Sheet1").Activate
Range("A1:H8").Formula = "=Rand()" 

• Range(cell1, cell2), where cell1 and cell2 are Range objects that specify the start and end cells (in a with statement)

' sets the border line style for cells A1:J10
With Worksheets(1)
    .Range(.Cells(1, 1), _
    .Cells(10, 10)).Borders.LineStyle = xlThick
End With

3D

A 3D range is the same range defined on more than one sheet.

To define it, you use the Array function (to specify two or more sheets)

Sub FormatSheets() 
 Sheets(Array("Sheet2", "Sheet3", "Sheet5")).Select 
 Range("A1:H1").Select 
 Selection.Borders(xlBottom).LineStyle = xlDouble 
End Sub

See also the FillAcrossSheets method that transfers the formats and any data from the range on Sheet2 to the corresponding ranges on all the worksheets in the active workbook.

Sub FillAll() 
 Worksheets("Sheet2").Range("A1:H1") _ 
 .Borders(xlBottom).LineStyle = xlDouble 
 Worksheets.FillAcrossSheets (Worksheets("Sheet2") _ 
 .Range("A1:H1")) 
End Sub

Naming

Worksheets(1).Range("Criteria").ClearContents

Function

Offset

Offset Property

Use Offset(row, column), where row and column are the row and column offsets, to return a range at a specified offset to another range.

Example:

'Can't select unless the sheet is active
Worksheets("Sheet1").Activate

' selects the cell:
'  * three rows down from 
'  * and one column to the right
' from the cell in the upper-left corner of the current selection. 
Selection.Offset(3, 1).Range("A1").Select

Union

union method

Use Union(range1, range2, …) to return multiple-area ranges (ie ranges composed of two or more contiguous blocks of cells).

Dim r1 As Range, r2 As Range, myMultiAreaRange As Range
Worksheets("sheet1").Activate
Set r1 = Range("A1:B2")
Set r2 = Range("C3:D4")
Set myMultiAreaRange = Union(r1, r2)
myMultiAreaRange.Select

Area

If you work with selections that contain more than one area, the Areas property is very useful.

It divides a multiple-area selection into individual Range objects and then returns the objects as a collection.

Sub NoMultiAreaSelection()
    NumberOfSelectedAreas = Selection.Areas.Count
    If NumberOfSelectedAreas > 1 Then
        MsgBox "You cannot carry out this command " & _
            "on multi-area selections"
    End If
End Sub