Excel VBA: Loop Through Columns in Range (5 Examples)

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.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


5 Examples to Loop Through Columns in Range Using VBA in Excel

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.

Excel VBA Loop Through Columns in Range

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

Excel VBA Loop Through Columns in Range

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.

Excel VBA Loop Through Columns in Range

Read More: How to Use VBA to Count Rows in Range with Data in Excel (5 Macros)


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.

Excel VBA Loop Through Columns in Range

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 Sub

Output: The VBA code found two values that are greater than 90 in the Quantity column and colored the entire column’s font color red.

Excel VBA Loop Through Columns in Range

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 in Range in Excel (6 Examples)


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

Excel VBA Loop Through Columns in Range

Output: The VBA code deleted the targeted columns from the dataset successfully.

Excel VBA Loop Through Columns in Range


Similar Readings


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

Excel VBA Loop Through Columns in Range

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.

Excel VBA Loop Through Columns in Range

Read More: Excel VBA: Get Range of Cells with Values (7 Examples)


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.

Excel VBA Loop Through Columns in Range

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.

Excel VBA Loop Through Columns in Range

Read More: VBA to Loop through Rows and Columns in a Range in Excel (5 Examples)


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.

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.


Related Articles

Al Arafat Siddique

Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo