Excel Macro: Loop Through a List of Data

When you write a VBA Macro, you may have to loop through a list of data on a worksheet, this is one of VBA's must-have skills.

Your data might be a list of the following types:

• A list that contains a known constant number of rows or columns
• A dynamic list
• A list with an unknown number of rows
• A list with an unknown number of columns

Loop Through a List of Data

'------------------ Modules ------------------
Sub LoopData()
'Step 1:  Declare your variables.
    Dim MyRange As Range
    Dim MyCell As Range

'Step 2:  Define the target Range.
    Set MyRange = Range("A1:F7")
'    Set MyRange = Range("A1", Range("A" & Rows.count).End(xlUp))
'    Set MyRange = Range("A1", Range("A1").End(xlDown))
'    Set MyRange = Range("A1", Cells(1, Columns.count).End(xlToLeft))
'    Set MyRange = Range("A1", Range("A1").End(xlToRight))
'    Set MyRange = Range("A1").CurrentRegion
'    Set MyRange = ActiveSheet.UsedRange
    
'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
'            Exit For
        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. The code example assumes that the list data is as follows:

You can assign values to variables using any of the following methods:

Specific range, result: range A1:F7

Set MyRange = Range("A1:F7")

A column of non-contiguous data, result: range A1:A7

Set MyRange = Range("A1", Range("A" & Rows.count).End(xlUp))

A column of contiguous data, result: range A1:A5

Set MyRange = Range("A1", Range("A1").End(xlDown))

A row of non-contiguous data, result: range A1:F1

Set MyRange = Range("A1", Cells(1, Columns.count).End(xlToLeft))

A row of contiguous data, result: range A1:C1

Set MyRange = Range("A1", Range("A1").End(xlToRight))

Current region of range A1, result: range A1:C7

Set MyRange = Range("A1").CurrentRegion

Used range, result: range A1:F7

Set MyRange = ActiveSheet.UsedRange

Read more about Select a Range with Excel VBA

3. In this step, the macro uses For Each...Next statement starts looping through each cell in the target range, activating each cell as it goes through. Note that the index order is left-to-right, top-to-bottom, alphabetical z-order.

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, you may want to insert a row between each active cell, or check if a cell is empty.

In this example, the macro is changing the font to Bold for any cell that has a value greater than 100. if you uncomment line 22, ' Exit For, the macro will changing the font to Bold for first cell (C1) that has a value greater than 100.

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