Macro 33: Enumerating Through a Range of Cells
One must-have VBA skill is the ability to enumerate (or loop) through a range. If you do any serious
macro work in Excel, you will soon encounter the need to go through a range of cells one by one and
perform some action.
This basic macro shows you a simple way to enumerate through a range.
How it works
In this macro, we are essentially using two Range object variables. One of the variables captures the
scope of data we are working with, whereas the other is used to hold each individual cell as we go
through the range. Then we use the For Each statement to activate or bring each cell in the target
range into focus:
Sub Macro33()
‘Step1: Declare your variables.
Dim MyRange As Range
Dim MyCell As Range
‘Step 2: Define the target Range.
Set MyRange = Range(“D6:D17”)
‘Step 3: Start looping through the range.
For Each MyCell In MyRange
‘Step 4: Do something with each cell.
If MyCell.Value > 3000 Then
MyCell.Font.Bold = True
End If
‘Step 5: Get the next cell in the range
Next MyCell
End Sub
1. The macro first declares two Range object variables. One, called MyRange, holds the entire
target range. The other, called MyCell, holds each cell in the range as the macro enumer-
ates through them one by one.
2. In Step 2, we fill the MyRange variable with the target range. In this example, we are using
Range(“D6:D17”). If your target range is a named range, you could simply enter its
name — Range(“MyNamedRange”).
3. In this step, the macro starts looping through each cell in the target range, activating each cell
as it goes through.
4. After a cell is activated, you would do something with it. That “something” really depends
on the task at hand. You may want to delete rows when the active cell has a certain value,
or you may want to insert a row between each active cell. In this example, the macro is
changing the font to Bold for any cell that has a value greater than 3,000.
5. In Step 5, the macro loops back to get the next cell. After all cells in the target range are
activated, the macro ends.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11 on your keyboard.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code.
