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:
- First, declare a variable to use as a cell of the range to loop.
- After that, start the “For Each loop” and use “iCell” variable to represent each cell in the range while the loop.
- Next, you need to write a line of code to perform some activity to each cell we are looping the range.
- 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 that is just like the code we have 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 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
⇠ Back to What is VBA in Excel
Helpful Links – Developer Tab – Visual Basic Editor – Run a Macro – Personal Macro Workbook – Excel Macro Recorder – VBA Interview Questions – VBA Codes