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()
Dim rCell As Range
Dim rArea As Range
On Error GoTo ErrorHandle
Set rArea = Range("A1")
If Len(rArea.Offset(1, 0).Formula) > 0 Then
Set rArea = Range(rArea, rArea.End(xlDown))
End If
For Each rCell In rArea
With rCell
If IsNumeric(.Value) And .Value > 100 Then
.Interior.ColorIndex = 3
End If
End With
Next
BeforeExit:
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:
|