In this article, I offer two different ways to loop through rows of a table or range in Excel VBA. Looping through rows is a common task in VBA so it’s good to have a strong grasp of the code.
To start off, I have chosen to work with a file that has names, addresses, and unformatted phone numbers. A preview of the data is below.
I have also added a function to do the transformation from unformatted to formatted phone number. The function is below.
Function FormatAsPhoneNumber(n As String) As String
'takes a string with 10 digits and adds parens and dash
FormatAsPhoneNumber = "(" & Left(n, 3) & ") " & Mid(n, 4, 3) & "-" & Right(n, 4)
End Function
Looping Through Rows with a For Each Loop
Sub FormatAsPhoneNumbersForEachLoop()
Dim table As Range
' table is the range I want to loop through rows. Other ways to get such a range may be to use a named range or static range.
' This method depends on having at least one row in table and no blank rows.
Set table = Range("A2", Range("A2").End(xlToRight).End(xlDown))
' loop through each row in table, with row as a range
For Each Row In table.Rows
' format the value in the third column as a phone number
' note that it is always selecting the first row because the range is restricted to the current row
Row.Cells(1, 3).Value = FormatAsPhoneNumber(Row.Cells(1, 3).Value)
Next Row
End Sub
The code above uses a for each loop, rather than a standard for loop, to loop through the rows. The variable Row in the For Each loop is a range representing a row. One advantage of this is that there is no need to select within the larger range. It’s also great where you wish to do something to the entire row at once, for example, a formatting operation. For this application however, I find it a bit clunky. Let’s look at using a standard For loop.
Looping Through Rows with a For Loop.
Sub FormatAsPhoneNumbersForLoop()
Dim table As Range
' table is the range I want to loop through rows. Other ways to get such a range may be to use a named range or static range.
' This method depends on having at least one row in table and no blank rows.
Set table = Range("A2", Range("A2").End(xlToRight).End(xlDown))
' loop through each row in table, using i to keep track of row
For i = 1 To table.Rows.Count
' format the value in the third column as a phone number
table(i, 3).Value = FormatAsPhoneNumber(table(i, 3).Value)
Next i
End Sub
In the above example, a standard For loop is used to loop through the range. Rather than receiving the row as a new range within the body of the loop, a counter is used which allows the row to be selected from the broader range. Overall, I personally prefer this application for operations like this where you wish to loop through each row and target a certain cell.
Conclusion
That’s two methods to loop through rows in a range or table! If you have any comments please comment below.