Excel VBA - Range

Card Puncher Data Processing

About

Range represents:

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





Discover More
Card Puncher Data Processing
Excel - ActiveCell

The ActiveCell property of a worksheet returns a Range object that represents the cell that is active. You can apply any of the properties or methods of a Range object to the active cell Be careful to...
Card Puncher Data Processing
Excel - Cell

In Microsoft Excel, you usually select a cell (see active cell) or cells and then perform an action, such as formatting the cells or entering values in them. In Visual Basic, it is usually not necessary...
Card Puncher Data Processing
Excel VBA - Loop

... loop through a range with the cells method. ... loop through a range is to use a For Each...Next loop with the collection of cells specified in the Range property



Share this page:
Follow us:
Task Runner