Excel VBA - Range

1 - About

Range represents:

3 - Range

Range property:

3.1 - Address

3.1.1 - 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


  • Active Worksheet


  • 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()"

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

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

3.1.3 - 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 
 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") _ 
End Sub

3.2 - Naming


4 - Function

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


'Can't select unless the sheet is active

' 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

4.2 - 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
Set r1 = Range("A1:B2")
Set r2 = Range("C3:D4")
Set myMultiAreaRange = Union(r1, r2)

4.3 - 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

Data Science
Data Analysis
Data Science
Linear Algebra Mathematics

Powered by ComboStrap