Method 1 – Loop Through Columns to Find a Value Range Using VBA in Excel
A large dataset to work with. In the dataset, Column A holds the order dates.
Solution: To find 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 code, we could run a loop through a column to find and select our desired values.
Method 2 – Run a VBA Code to Loop Through Columns in Range and Add Conditional Formatting
Task: 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: 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 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. The dataset, we’ll be working with.
Method 3 – Loop Through Columns in Range to Delete Them with Excel VBA
Task: Delete the columns named City and Category (i.e., columns no 2 and 3) from the dataset.
Solution: 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.
Method 4 – Find and Replace a Value by Looping Through Columns in Range with Excel VBA
Task: Change the unit price of the product Carrot from 1.77 to 1.5 in the range A1:G9.
Solution: 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.
Method 5 – Define Range to Loop Through Columns Using VBA in Excel
Task: 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.
- 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”.
- Put 77 in the input box named “Put the Value to search in Range:”
- 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.
Related Articles
Get FREE Advanced Excel Exercises with Solutions!
