Loop Through a Range using VBA (Columns, Row, and UsedRange)

HomeVBALoop Through a Range using VBA (Columns, Row, and UsedRange)

To loop through a range in VBA, you need to use the “For Each Loop”. By using this loop, you can write a code where you tell VBA to go through each cell in the range, column, or row and perform a specific activity.

Sub vba_loop_range()

Dim iCell As Range

For Each iCell In Range("A1:A10").Cells
    iCell.Value = "Yes"
Next iCell

End Sub

Following are steps for looping through a range:

  1. First, declare a variable to use as a cell of the range to loop.
    1-loop-through-range
  2. After that, start the “For Each loop” and use “iCell” variable to represent each cell in the range while the loop.
    2-use-icell-variable
  3. Next, you need to write a line of code to perform some activity to each cell we are looping the range.
    3-write-a-line-of-code
  4. In the end, end the code for a loop.
    4-end-the-code-for-a-loop

Loop Through Entire Column and a Row using VBA

In the same way, you can loop through each cell of a row using the following code that is just like the code we have used in the earlier example, the only difference is the range that we have specified.

loop-through-entire-column-and-row

Note: Before you loop through an entire column or a row you need to understand that it can take quite a time to complete the loop and it can make your Excel freeze for few minutes.

Sub vba_loop_range()

Dim iCell As Range

For Each iCell In Range("A:A").Cells
    iCell.Value = "Yes"  
Next iCell

End Sub

And in the same way, you can use a full row as a range to loop through.

Sub vba_loop_range()

Dim iCell As Range

For Each iCell In Range("1:1").Cells
    iCell.Value = "Yes"  
Next iCell

End Sub

Loop Through Dynamic Range

And if you want to loop through a dynamic range of cells refer to the following code.

loop-through-dynamic-range
Sub vba_dynamic_loop_range()

Dim iCell As Range
Dim iRange1 As String
Dim iRange2 As String
Dim rangeName As String

iRange1 = ActiveCell.Address
iRange2 = ActiveCell.Offset(5, 5).Address
rangeName = iRange1 & ":" & iRange2

For Each iCell In Range(rangeName).Cells

    iCell = "Yes"
   
Next iCell

End Sub