Range is one of the most widely used objects in Excel VBA, as it allows the manipulation of a row, column, cell or a range of cells in a spreadsheet.

When recording absolute macros, a selection of methods and properties use this object:

Range("A1").Select

Range("A1").FormulaR1C1 = 10

A generic global object known as Selection can be used to determine the current selection of a single or range cells.

When recording relative macros, a selection of methods and properties use this object:

Selection.Clear

Selection.Font.Bold = True

There are many properties and methods that are shared between Range and Selection objects and below are some illustrations (my choice of commonly used identifiers):

ADDRESS Property

Returns or sets the reference of a selection.

Sub AddressExample()
    MsgBox Selection.Address '$A$1 (default) - absolute
    MsgBox Selection.Address(False, True) '$A1 - column absolute
    MsgBox Selection.Address(True, False) 'A$1 - row absolute
    MsgBox Selection.Address(False, False) 'A1 - relative

End Sub

AREAS Property

Use this property to detect how many ranges (non-adjacent) are selected.

'Selects three non-adjacent ranges
Sub AreaExample()
Range("A1:B2", E4, G10:J25").Select
MsgBox Selection.Area.Count 'Number '3' - ranges returned
End Sub

The Count method returns the number selected as the Areas is a property only.

'Check for multiple range selection
Sub AreaExample2()
    If Selection.Areas.Count > 1 Then
        MsgBox "Cannot continue, only one range must be selected."
        Exit Sub
    End If

[Code continues here...]
End Sub

Use the Areas property to check the state of a spreadsheet. If the system detects multiple ranges, a prompt will appear.

CELLS Property

This property can be used as an alternative to the absolute range property and is generally more flexible to work with, as variables are easier to pass into it.

There are two optional arguments:

Cells([row] [,column])

Leaving the arguments empty (no brackets), it will detect the current selection as the active range.

Adding an argument to either row or column with a number will refer to the co-ordination of the number passed.

Adding both arguments will explicitly locate the single cell’s co-ordinate.

'Examples of the Cells property
Sub CellsExample()
    Cells.Clear 'clears active selection
    Cells(1).Value = "This is A1 - row 1"
    Cells(, 1).Value = "This is A1 - col 1"
    Cells(1, 1).Value = "This is A1 - explicit"
    Cells(3, 3).Value = "This is C3"
    Cells(5, 3).Font.Bold = True
End Sub

Variables can be passed into the Cells property and then nested into the Range object as in the following example:

'Two InputBoxes for rows and columns
Sub CellsExample2() 
    On Error GoTo handler
    Dim intRows As Integer
    Dim intCols As Integer
    intRows = CInt(InputBox("How many rows to populate?"))
    intCols = CInt(InputBox("How many columns to populate?"))
    'starts at cell A1 to the number of rows and columns passed
    Range(Cells(1, 1), Cells(intRows, intCols)).Value = "X"
    Exit Sub
handler:
    'Error code is handled here...
End Sub

By wrapping a range property around two cell properties, the flexibility of passing variables becomes apparent.

Range(Cells(1, 1), Cells(intRows, intCols))

Error handlers and InputBox functions are covered later in this guide.

Column(s) and Row(s) Properties

Four properties that return the column or row number for the focused range.

The singular (Column or Row) returns the active cell’s co-ordinate and the plural (Columns or Rows) can be used to count the current selections configuration.

Sub ColRowExample()
MsgBox "Row " & ActiveCell.Row & _
" : Column " & ActiveCell.Column

End Sub

Sub ColsRowsCountExample()
MsgBox Selection.Rows.Count & " rows by " _
& Selection.Columns.Count & " columns selected"

End Sub

CURRENTREGION Property

Selects from the active cell’s position all cells that are adjacent (known as a region) until a blank row and blank column breaks the region.

Use this statement to select a region.

Selection.CurrentRegion.Select

Make sure you have some data to work with.

To select a region of data and exclude the top row for a data list:

Run this piece of code:

Sub RegionSelection()
    ActiveCell.CurrentRegion.Offset(1, 0).Resize( _
        ActiveCell.CurrentRegion.Rows.Count - 1, _
            ActiveCell.CurrentRegion.Columns.Count).Select

End Sub

Make sure the active cell is in the region of data you wish to capture before running the above procedure.

RESIZE Property

This property is useful for extending or re-defining a new size range.

To extend this range

by one row and one column to

use the code snippet below:

Sub ResizeRange()
       Dim rows As Integer
    Dim cols As Integer
    cols = Selection.Columns.Count
    rows = Selection.rows.Count
    Selection.Resize(rows + 1, cols + 1).Select

End Sub

Resizing a range can be increased, decreased or change the configuration (shape) by combining positive and negative values inside the Resize property’s arguments.

OFFSET Property

This property is used in many procedures as it controls references to other cells and navigation.

Two arguments are passed into this property that is then compounded with either another property or a method.

Selection.OffSet(1, 2).Select

ActiveCell.OffSet(0, -1).Value = "X"

Consider referring to an offset position rather than physically navigating to it – this will speed up the execution of code particularly while iterating.

For example:

Sub OffSetExample1()
    Dim intCount As Integer
    Do Until intCount = 10
        ActiveCell.Offset(intCount, 0).Value = "X"
        intCount = intCount + 1
    Loop

End Sub

is quicker to execute than:

Sub OffSetExample2()
    Dim intCount As Integer
    Do Until intCount = 10
        ActiveCell.Value = "X"
        ActiveCell.Offset(1, 0).Select
        intCount = intCount + 1
    Loop

End Sub

Do...Loops (iterations) are covered later in this guide

The above two examples produce the same result but instead of telling Excel to move to the active cell and then enter a value, it is more efficient to refer (or point) to the resulting cell and remain in the same position.

A positive value for the row argument refers to a row downwards.
A positive value for the column argument refers to a column to its right.
A negative value for the row argument refers to a row upwards.
A negative value for the column argument refers to a column to its left.

Be careful to introduce error-handling procedures when working with the ‘Offset’ property as if you navigate or refer to a position outside the scope of the fixed dimensions of a worksheet, this will certainly cause a run time error (See Error Handling & Debugging).

ACTIVATE Method

This method should not be confused with the Select method as commonly used in VBA.

The Select method means go and navigate to it.

Range(“A1”).Select.

Range(“A1:C10”).Select

The Activate method selects a cell within a selection.

By default, in a selection of cells, the first (top left position) is the active cell (white cell in a block).

Example:

Sub ActivateMethodExample()
    'select a fixed range
    Range("A1:C10").Select
    MsgBox ActiveCell.Address(False, False)
    Range("B2").Activate
    MsgBox ActiveCell.Address(False, False)
End Sub

The above procedure selects a fixed range of cells with a message box confirming the address of the active cell. Then, using the Activate method, move the active cell to address B2.

From

CLEAR Methods

There are six variations of this method:

Clear – all attributes are cleared and reset to default
ClearComments – clear comments only
ClearContents – clear contents only (delete key command)
ClearFormats – clear formats only (revert to general format)
ClearNotes – clear comments and sound notes only
ClearOutline – clear on outlines implemented

Simply locate the object and use of the above methods:

'Different ways to refer to a selection
Sub ClearMethodsExamples()
    Range("A1:C10").Clear
    Selection.ClearComments
    Selection.CurrentRegion.ClearContents
    ActiveCell.ClearFormats
    Range(Cells(1, 1), Cells(5, 3)).ClearNotes
    Columns("A:E").ClearOutline
End Sub

CUT, COPY and PASTESPECIAL Methods

These methods simulate the windows clipboard cut, copy and paste commands.

There are a few different types of these methods where most arguments are optional and by changing the argument settings, will change the behaviour of the method.

Some examples:

'Simple Copy and Paste
Sub CopyPasteData1()
Range("A1").Copy
Range("B1").PasteSpecial xlPasteAll
End Sub

'Copy and Paste Values only (no format)
Sub CopyPasteData2()
Range("A1").Copy
Range("B1").PasteSpecial xlPasteValues
End Sub

'Simple Cut and Paste
Sub CutPasteData()
Range("A1").Cut Range("B1")
End Sub

If the copy and cut methods omit the argument Destination, the item is copied to the windows clipboard.

INSERT and DELETE Methods

These methods can add or remove cells, rows or columns and is best used with other properties to help establish which action to execute.

Some examples:

'Inserts an entire row at the active cell
Sub InsertRow()
ActiveCell.EntireRow.Insert 'or EntireColumn
End Sub

'Deletes an entire row at the active cell
Sub DeleteRow()
ActiveCell.EntireRow.Delete 'or EntireColumn
End Sub

'Inserts an entire row at row 4
Sub InsertAtRow4()
ActiveSheet.rows(4).Insert
End Sub

'Insert columns and move data to the right
Sub InsertColumns()
Range("A1:C5").Insert Shift:=xlShiftToRight
End Sub

Using the SET Keyword Command

Users can create and set a range object instead and like all other object declarations, use the Set command (which is used for object variable declarations).

'Alternative way of referring to a range
Sub RangeObject()
    Dim rng As Range
    Set rng = Range("A1:B2")
    With rng
        .Value = "X"
        .Font.Bold = True
        .Borders.LineStyle = xlDouble
        'any other properties.........
    End With
    Set rng = Nothing
End Sub

This is an alternative way of working with the range object and is sometimes preferred as it exposes more members (properties and methods).

For example, using a For...Loop (see For...Loop section about this control flow), iterating in a collection is carried out by declaring and setting a variable as a Range object:

'Loops through the elements of the Range object
Sub IterateRangeObject()
    Dim r1 As Range
    Dim c As Object
    Set r1 = Range("A1:C10")
    For Each c In r1
        If c.Value = Empty Then
            c.Value = "0"
        End If
    Next c
End Sub

The above procedure checks each cell in a fixed range (A1 to C10) and determines its value, placing a 0 (zero) if the cell is empty.

Next Topic: Object hierarchy

Want to teach yourself Access? Free online guide at About Access Databases

Tip: You can refer to Range("A1") using the convention [A1] which may be easier to write.

VBA Keywords: Range, Select, Clear, Font, Bold, Address, Selection, MsgBox, Area, Count, Cells, InputBox, CurrentRegion, OffSet, Resize, Columns, Rows, Column, Row, Dim, Activate, ClearFormats, ClearComments, ClearContents, ClearNotes, ClearOutline, Cut, Copy, PasteSpecial, Insert, Delete, EntireRow, Set, Borders, Interior, Do...Loop, For...Next and If...Then