Excel VBA Loop Through Rows, Cells, or Columns

Using VBA to loop through rows, cells and columns can transform the way you handle your spreadsheets. In this post, I’ll cover a few ways you can achieve this with some helpful examples.

The key to looping through any of these objects is the Range object. The Range object represents a cell, or a selection of cells. With this object and some VBA, we can loop through cells in any imaginable fashion

Loop Through Rows

To loop through rows, open your spreadsheet and create a macro. Then, paste in the following code:

Sub LoopThrough()
For Each Row In Range("A1:G5")
  Row.RowHeight = 8
Next
End Sub

Running this does:

As you can see, the macro went through each row specified in the range value A1:G5 and then adjusted the Row height for each of the rows.

This is done using the For Each… method in VBA. For Each allows you to specify a collection of objects and iterate over each of them. There are a few nuances to how it works and how you can iterate over Rows versus Columns. In the example above, Rows are simply the default behavior of iterating over a range, so no extra considerations is needed.

Loop Through Columns

To lop through columns, you will have to use the Column property of the Range you are iterating over and the Cell property of the Column:

Sub LoopThrough()

For Each Column In Range("A1:G5").Columns
  For Each Cell In Column.Cells
    Cell.Value = 1
  Next
Next
End Sub

Iterating over Columns is a bit trickier, but can be done with a few tweaks to our existing code.

Loop Through Cells

Looping through rows and columns can be useful, but you may ultimately be looking to loop through cells withing those structures. To loop through cells, you can use the same code structure as for Row and Columns, but within each Row and Column, you can iterate over the Cells in each of them:

Sub LoopThrough()

Dim CellValue As Integer

CellValue = 0

For Each Row In Range("A1:G5")
  For Each Cell In Row
    Cell.Value = CellValue
    CellValue = CellValue + 1
  Next
Next
End Sub

Using this code, we have the same Range, and we are going to go through each Row and then each Cell. We have defined the variable CellValue and each cell will get an incremental value of CellValue from the previous value:

If we switch this to go through Columns, instead of Rows, we get this:

Sub LoopThrough()

Dim CellValue As Integer

CellValue = 0

For Each Column In Range("A1:G5").Columns
  For Each Cell In Column.Cells
    Cell.Value = CellValue
    CellValue = CellValue + 1
  Next
Next
End Sub

Pretty cool! With these techniques, you can iterate over any structure you have in Excel.

Dynamic Ranges

One other interesting note about looping over Ranges is how you can set a dynamic value for the range. Say you wanted to have a macro that made cells fill in with values based on the value of two other cells (where you can specify the Range you want):

By entering values in ‘B1’ and ‘B2,’ you can make a table appear with values pre-filled. To do this, make a macro with this code and run it:

Sub LoopThrough()
Dim StringRange As String
StringRange = "B4:" + Worksheets("Sheet1").Cells(2, 2).Value + CStr(3 + Worksheets("Sheet1").Cells(1, 2).Value)
For Each Row In Range(StringRange)
    For Each Cell In Row
        Cell.Value = "Cell"
    Next
Next
End Sub

By specifying a number of Rows and a letter for the columns to end and putting in a dummy value for the Cells, you can then have something like this: