Loop 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.
   
2. After that, start the “For Each loop” and use “iCell” variable to represent each cell in the range while the loop.
   
3. Next, you need to write a line of code to perform some activity to each cell we are looping the range.
   
4. In the end, 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 which is just like the code we used in the earlier example, the only difference is the range that we have specified.

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 a 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.

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