Microsoft Excel Training Videos

A macro in Microsoft Excel to loop through a range of Excel cells using 'for... next'

When using Visual Basic for Applications in Microsoft Excel, you often need to run the same block of statements on each Excel cell in a range of cells. To do this, you combine a looping statement and one or more methods to identify each cell, one at a time, and run the operation.
One way to loop through a range in Excel is to use the For...Next loop with the Cells property. Using the Cells property, you can substitute the loop counter (or other variables or expressions) for the cell index numbers. In the following example, the variable counter is substituted for the row index. The procedure loops through the Excel range C1:C10, rounding all the decimal numbers to one decimal place as shown in the Excel training video below.

Sub RoundToOneDecimalPlace()
For Counter = 1 To 10
Set curCell = Worksheets("Sheet1").Cells(Counter, 3)
curCell.Value = round((curCell.Value), 1)
Next Counter
End Sub
Another easy way to loop through a range in Excel is to use a For Each...Next loop with the collection of Excel cells specified in the Range property. Visual Basic automatically sets an object variable for the next cell each time the loop runs. The following procedure loops through the Excel range c2:D9, rounding all decimal values to one decimal place.
Sub myround()
For Each c In Worksheets("sheet1").Range("c2:d9").Cells
c.Value = round((c.Value), 1)
Next
End Sub


If you don't know the boundaries of the Excel range you want to loop through, you can use the CurrentRegion property in Excel to return the range that surrounds the active Excel cell. For example, the following procedure, when run from an Excel worksheet, loops through the range that surrounds the active Excel cell, rounding all decimal numbers to one decimal place.
Sub RoundToOneDecimalPlace()
For Each c In ActiveCell.CurrentRegion.Cells
c.Value =round((c.value),1)
Next
End Sub
Further links
Loops in Excel
Looping through a range of cells