Loop Through a Range of Cells

One must-have VBA skill is the ability to loop (or enumerate) through a range of cells. 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 loop through a range of cells.

Loop Through a Range of Cells Full VBA Code

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 LoopRange()
'Step 1:  Declare your variables.
    Dim MyRange As Range
    Dim MyCell As Range
'Step 2:  Define the target Range.
    Set MyRange = Range("A1:D10")
'Step 3:  Start looping through the range.
    For Each MyCell In MyRange
'Step 4:  Do something with each cell.
    If MyCell.Value > 100 Then
        MyCell.Font.Bold = True
    End If
'Step 5: Get the next cell in the range
    Next MyCell
End Sub

How This Macro Works

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 enumerates 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(“A1:D10”). If your target range is a named range, you could simply enter its name — Range("MyNamedRange"), see also: Creating, Selecting and Formatting Named Ranges Via VBA.
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 100, see the screenshot:
   
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 This Macro

To use this macro, you can copy and paste it into a standard module:

1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert -> Module.
   
4. Type or paste the code in the newly created module.