In this article, we’ll illustrate how to loop through columns in a predefined range using VBA code in Excel with 5 suitable examples. Let’s dive into them to get a clear understanding of the topic.
Loop Through Columns in Range Using VBA in Excel: 5 Examples
In the following examples, we’ll use loops to handle the repetitive tasks without writing the code block again and again. We’ll use the For Each Next Loop and the For Next Loop to illustrate the examples.
To loop through columns in range, we’ll use several VBA functions and properties in our code. The following section describes how to open and write code in the visual basic editor.
Write Code in Visual Basic Editor
Follow the steps to open the visual basic editor and write some code there.
- Go to the Developer tab from the Excel Ribbon.
- Click the Visual Basic option.
- In the Visual Basic For Applications window, click the Insert dropdown to select the New Module option.
Now that a new module is opened, write some code there and press F5 to run.
1. Loop Through Columns to Find a Value in Range Using VBA in Excel
Let’s say we have a large dataset to work with. In the dataset, Column A holds the order dates.
Task: We want to find and select a specific date (3/11/2020, in this example) to see the order details.
Solution: To find out the date, we need to run a loop through the entire column A in the dataset. To do this, copy and paste the following code into the visual basic editor. Press F5 to run the code.
Sub LoopColumnsInRange()
Dim cell As Range
For Each cell In Range("A:A")
If cell.value = "3/11/2020" Then
cell.Select
MsgBox "Date found at " & cell.Address
End If
Next cell
End Sub
Output: The VBA code performed a loop through Column A and found the targeted date at cell A25. With this piece of code, we could run a loop through a column to find and select our desired values.
2. Run a VBA Code to Loop Through Columns in Range and Add Conditional Formatting
In this example, we’ll be working on a part of the dataset, say, the first 10 rows to visualize easily.
Task: We want to detect those cells in the Quantity column having a quantity of more than 90 and then change the font color of the entire row to red.
Solution: To accomplish the task, copy and paste the following code and press F5 to run it.
Sub LoopColumnsInRange()
Dim cell As Range
For Each cell In Range("E2:E10")
If cell.value > 90 Then
cell.EntireRow.Font.Color = vbRed
End If
Next cell
End SubOutput: The VBA code found two values that are greater than 90 in the Quantity column and colored the entire column’s font color red.
This conditional formatting will help the user to understand and visualize the dataset easily. Here is the dataset, we’ll be working with.
Read More: VBA to Loop through Rows and Columns in a Range in Excel
3. Loop Through Columns in Range to Delete Them with Excel VBA
In this example, we’ll loop through columns in a range to delete them using VBA in Excel.
Task: We want to delete the columns named City and Category (i.e., columns no 2 and 3) from the dataset.
Solution: To delete, columns 2 and 3 from the dataset, copy and paste the following VBA code in the visual basic editor.
Sub DeleteColumns()
Dim i As Integer
For i = 3 To 2 Step -1
Columns(i).Delete
Next i
End Sub
Output: The VBA code deleted the targeted columns from the dataset successfully.
4. Find and Replace a Value by Looping Through Columns in Range with Excel VBA
In this example, we’re going to find a specific text or value in a range of cells and then replace it with another.
Task: Let’s change the unit price of the product Carrot from 1.77 to 1.5 in the range A1:G9.
Solution: Let’s copy and paste the following code, to make this happen.
Sub LoopThroughColumnsInRange()
For Each Column In Range("A1:G9").Columns
For Each cVal In Column.Cells
If cVal.value = 1.77 Then
cVal.value = 1.5
End If
Next
Next
End Sub
Output: The VBA code looped through all the cells by column within the range and changed the cells with values from 1.77 to 1.5 successfully.
5. Define Range to Loop Through Columns Using VBA in Excel
In this illustration, we’re going to perform the same task as in Example 4 i.e., to change the unit price of Carrot from 1.77 to 1.5 in our defined range. But this time the dataset is in a different arrangement.
Task: We want to change the unit price of the product Carrot in the range A1:G7 from 1.77 to 1.5.
Solution: Set the range A1:G7 in the code as we want to run a loop within these columns. Follow the steps below:
- Copy and paste the following code into the visual basic editor.
Sub LoopColumnsInRange()
Dim cell As Range
Dim colRange As String
Dim cellValue As Double
Dim changeValue As Double
colRange = Application.InputBox(Prompt:="Range to Loop through: ", Type:=2)
cellValue = Application.InputBox(Prompt:="Put the Value to search in Range: ", Type:=1)
changeValue = Application.InputBox(Prompt:="Put the Value to Replace with: ", Type:=1)
For Each cell In Range(colRange)
If cell.value = cellValue Then
cell.value = changeValue
End If
Next cell
End Sub- Press F5 to run the code.
- Put A1:G7 in the input box named “Range to Loop through”.
- Then put77 in the input box named “Put the Value to search in Range:”
- Finally, put 55 in the input box named “Put the Value to Replace with: “.
Output: The output is in the following screenshot.
Notes
- In example 1, we used the MsgBox function to show the output
- In example 5, we used the Application.InputBod method of Excel to get the user input in the VBA code. To get a number as input we used 1 as the input type in the code and similarly 2 for getting string value.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
Now, we know how to loop through columns in a range in Excel using VBA code. Hopefully, it would help you to use these methods more confidently. Any questions or suggestions don’t forget to put them in the comment box below.
