RSS

The Range object in Excel VBA macros

In Excel a Range represents any number of cells in a worksheet.

It is an extremely handy and flexible thing to use when programming. A range can be changed dynamically, and it is far more flexible than to operate with fixed cell addresses or "selected" cells.

You can loop through a range using the fast For Each...Next loop, and ranges have all sorts of useful properties like e.g. the Count property that will tell you the number of cells in the range.

Below is a simple example. To test it, highlight the code with the mouse, copy (CTRL+C) and paste (CTRL+V) into a VBA module. In the worksheet's cells A1 to A5 you write:

120
20
Peter
40
500

The procedure will make the cells red, if their numeric value is greater than 100, but the "colour code" can easily be replaced by something else.


Sub PaintCells()
'Loops through cells in a column. Cells with
'a value greater than 100 are painted red.
Dim rCell As Range
Dim rArea As Range

On Error GoTo ErrorHandle

'The range variable is set = cell A1
Set rArea = Range("A1")

'If the cell below (A2) has content, rArea is expanded
'downwards to the last cell with content. This is like
'pressing CTRL + SHIFT + down arrow.
If Len(rArea.Offset(1, 0).Formula) > 0 Then
   Set rArea = Range(rArea, rArea.End(xlDown))
End If

'We now loop through the range cell by cell.
'If the value is numeric, and if it is
'greater than 100, the cell is painted red.
For Each rCell In rArea
   With rCell
      If IsNumeric(.Value) And .Value > 100 Then
         .Interior.ColorIndex = 3
      End If
   End With
Next

BeforeExit:
'Set range variables to nothing to avoid memory leak.
Set rCell = Nothing
Set rArea = Nothing

Exit Sub
ErrorHandle:
MsgBox Err.Description & ", Procedure PaintCells", "Error"
Resume BeforeExit
End Sub

If you have tried to record a macro, you have probably noticed that the auto-generated code uses "Selection". When using a selection you operate directly on the cells.

Using Ranges instead is much faster and more efficient, and it pays off to play around with the range object and explore its capabilities.

A Range is in fact a collection of cells and as a collection object it has some of the collection's properties. For instance you can loop through it using For Each...Next, and each cell can be addressed as "Item(number)".

Related: