How to Loop through each cell in Excel range?

Sometimes the data range in a Excel is dynamic. In those cases we cannot use the .Cells(Row,Col) notation. We have to use the below method to read with loop for each cell range.

Luckily, VBA has a range object and hence it already has a colection of cells within this data type in this image.

This makes it easy for programmers to traverse thrugh each cell in that range.

Excel VBA Code to Loop thru Each cell in a range - For command
Excel VBA Code to Loop thru Each cell in a range – For command

Here is a sample code snippet. You can copy this code, change the range as per your requirements and use it as is.

Excel VBA For each cell in Range – Loop structure

In Excel worksheet, a range can be defined as a collection of cells. For example, if we select cells from A1 to D4, then essentially we are grouping these 16 cells into the range(A1:D4).

To read each cell in this selection, we need to create a looping structure that can pick cells one by one and get its properties.

The following code exactly does that.

Sub for_loop_cells_in_range()
    Dim iRange As Range
    Dim iCell As Range
    Set iRange = Sheet1.Range("A1:F10")
    'Loop cells
    For Each iCell In iRange.Cells
        'Print cell address, value, row, col
        Debug.Print iCell.Address & ", " & iCell.Value & ", " & iCell.Row & ", " & iCell.Column
End Sub

With the above code, we get any cell property like it values, row/column number, width, font color, background or interior color, border, cell width height, comments, hyperlink in it, visiblilty, formula and much more.

As you can see that it is not only the cell value, we can get anything about any of the cells. Now only read, we can also set any of the properties to a different value.

Here are some more reading suggestions:

  1. Excel VBA Get Username – 6 Easy Ways using Macro
  2. VCF to Excel Converter – VBA Code to Read vCard File
  3. Excel To VCF Converter – 81,000+ Downloaded – Excel To Vcard App

External reference with similar topic:

  1. How to loop thru a range of cells?