You need to use the “For Each Loop” to loop through a range in VBA. Using this loop, you can write a code telling VBA to go through each cell in the range, column, or row and perform a specific activity.
Each loop in VBA goes through each item in a collection, like every cell in a range. It’s useful when you want to perform the same action on multiple items.
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, 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 row, you need to understand that it can take quite a time to complete the loop, and it can cause your Excel to 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
- Dim iCell As Range: Declares a variable iCell to represent each cell.
- For Each iCell In Range(“A:A”).Cells: Starts a loop to go through every cell in column A.
- iCell.Value = “Yes”: Sets the current cell’s value to “Yes”.
- Next iCell: Moves to the next cell in column A and repeats the loop until all cells are updated.
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
Run a Macro in Excel – To use any of the codes I have shared in this tutorial, you must know how to run a code. To open the VBA Editor, press Alt + F11, then paste your macro code. After that, run it to the “Developer” tab on the Excel ribbon. In the “Developer” tab, click on the “Macros” button. A list of available macros will appear. Select the macro you want to run and click “Run.”
Loop Through Dynamic Range
If you are working with data, there might be a chance to have data updated in the future, and if that’s the case, then you need to have a code that can update the range whenever required. That means you need to have a code with a 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
This VBA code dynamically creates a range based on the active cell, a cell five rows down, and a cell five columns to the right. It then loops through each cell in this range and sets its value to “Yes”.
First, the code declares variables for each cell (iCell), the starting and ending addresses of the range (iRange1 and iRange2), and the full range (rangeName).
It sets iRange1 to the address of the currently active cell and iRange2 to the address of the cell that is five rows down and five columns to the right from the active cell. It combines these two addresses into a range string (rangeName).
The For Each loop goes through each cell in this defined range and sets its value to “Yes”. Finally, the Next iCell moves to the next cell in the range, repeating the loop until all cells are updated.
Loop Through a Used Range and Check IF the Cell has a Formula
This VBA code loops through all the cells in the active worksheet’s used range and highlights any cell that contains a formula with a yellow cell color.
Sub highlight_formulas()
Dim cell As Range
Dim usedRange As Range
' Set the used range of the active sheet
Set usedRange = ActiveSheet.UsedRange
' Loop through each cell in the used range
For Each cell In usedRange
' Check if the cell contains a formula
If cell.HasFormula Then
' Highlight the cell with a yellow background
cell.Interior.Color = vbYellow
End If
Next cell
End Sub
It starts by declaring two variables: cell, for each cell in the loop, and usedRange, for the range of cells have data on the active sheet. The usedRange property includes all the cells used in the active worksheet.
Next, the code uses a For Each loop, which iterates through each cell within the usedRange. Inside the loop, it checks if the current cell contains a formula using the HasFormula property.
If the cell does have a formula, its background color is changed to yellow by setting the Interior.Color property to vbYellow.
The loop continues until all cells in the used range have been checked and highlighted if they contain a formula.
Loop Through a Range and Clear Contents
This code clears the contents of each cell in the range A1 to A10. It doesn’t change the formatting or appearance of the cells; it just removes any text or numbers inside.
Sub LoopClearContents()
Dim cell As Range
For Each cell In Range("A1:A10")
cell.ClearContents
Next cell
End Sub
The code starts by defining a variable named “cell” to loop through each cell in the range. Then, it uses a loop to go through each cell in the range A1 to A10. Inside the loop, it clears the contents of the current cell. The loop continues until all cells in the range have cleared their contents.
Loop Through a Range and Check for Specific Value
This code checks each cell in the range A1 to A10 in the worksheet to see if it contains the word “Test.” If it does, it changes the background color of that cell to yellow.
Sub LoopCheckValue()
Dim cell As Range
For Each cell In Range("A1:A10")
If cell.Value = "Test" Then
cell.Interior.Color = vbYellow
End If
Next cell
End Sub
The code starts by defining a variable named “cell” to loop to each cell in the specified range. Then, it uses a loop to go through each cell in the range A1 to A10.
Within the loop, it checks if the cell’s value is “Test”. If the condition is met, the cell’s background color is yellow. Finally, the loop moves to the next cell and repeats until all cells in the range have been checked.
Loop Through a Range and Delete Blank Rows
Here is a VBA code example that loops through a range and deletes blank rows:
Sub DeleteBlankRows()
Dim cell As Range
Dim rng As Range
Dim delRange As Range
' Set the range to loop through
Set rng = Range("A1:A10")
' Loop through the range
For Each cell In rng
' Check if the cell is empty
If IsEmpty(cell.Value) Then
' Store the entire row in delRange if it is empty
If delRange Is Nothing Then
Set delRange = cell.EntireRow
Else
Set delRange = Union(delRange, cell.EntireRow)
End If
End If
Next cell
' Delete all the empty rows at once
If Not delRange Is Nothing Then
delRange.Delete
End If
End Sub
Loop Through a Range and Delete Hidden Rows
Here is a VBA code example that loops through a range and deletes hidden rows. It first checks each cell in the range to see if it is empty. If a cell is empty, the entire row that the cell belongs to is marked for deletion.
Sub DeleteHiddenRows()
Dim cell As Range
Dim rng As Range
Dim delRange As Range
' Set the range to loop through
Set rng = Range("A1:A10")
' Loop through the range
For Each cell In rng
' Check if the row is hidden
If cell.EntireRow.Hidden Then
' Store the entire row in delRange if it is hidden
If delRange Is Nothing Then
Set delRange = cell.EntireRow
Else
Set delRange = Union(delRange, cell.EntireRow)
End If
End If
Next cell
' Delete all the hidden rows at once
If Not delRange Is Nothing Then
delRange.Delete
End If
End Sub
It starts by defining variables for individual cells, the range to check, and the rows to be deleted. It sets the range from A1 to A10 and then loops through each cell in this range.
If a cell is found to be empty, the entire row is added to a collection of rows to be deleted. After checking all cells, all the collected empty rows are deleted in one go.
Loop Through a Range and Add Serial Numbers
Here is a VBA code example that loops through a range and adds serial numbers to each cell in the specified range. This code will add serial numbers starting from 1 in the range A1 to A10.
Sub AddSerialNumbers()
Dim cell As Range
Dim i As Integer
' Initialize the serial number
i = 1
' Loop through the range A1 to A10
For Each cell In Range("A1:A10")
' Set the cell's value to the current serial number
cell.Value = i
' Increment the serial number
i = i + 1
Next cell
End Sub
This code adds serial numbers to the cells from A1 to A10. It starts with the number 1 and increases by 1 for each cell, so the first cell gets 1, the second cell gets 2, and so on up to 10.
The code defines two variables: “cell” for each cell in the range and “i” holding the current serial number. It initializes “i” to 1. Then, it loops through each cell in the range A1 to A10. For each cell, it sets the cell’s value to the current number in “i,” then increases “i” by 1.