Loops & Dynamic Ranges in VBA
Learn how to repeat VBA statements, process Excel rows automatically, find the last used row, work with dynamic ranges, and automate repetitive spreadsheet tasks.
1. What Is a Loop?
A loop is a programming structure that executes a block of statements repeatedly. Instead of writing the same statement for every Excel row, you can use a loop to process each row automatically.
For example, suppose an Excel worksheet contains 1,000 employees. A loop can calculate each employee's bonus without writing 1,000 separate instructions.
2. Types of Loops in VBA
| Loop | When to Use It |
|---|---|
For...Next |
When the number of iterations is known. |
For Each...Next |
When processing each object or item in a collection. |
Do While...Loop |
When repeating while a condition is True. |
Do Until...Loop |
When repeating until a condition becomes True. |
Do...Loop While |
When the loop must execute at least once before checking the condition. |
Do...Loop Until |
When the loop must execute at least once and then stop when the condition becomes True. |
3. For...Next Loop
Use For...Next when you know the starting value
and ending value of the loop counter.
Syntax
For counter = startValue To endValue
' Statements
Next counter
Example 1: Print numbers from 1 to 5
Sub PrintNumbers()
Dim i As Long
For i = 1 To 5
Debug.Print i
Next i
End Sub
Debug.Print displays the values in the Immediate
window of the VBA editor.
| Iteration | Value of i | Action |
|---|---|---|
| 1 | 1 | Print 1 |
| 2 | 2 | Print 2 |
| 3 | 3 | Print 3 |
| 4 | 4 | Print 4 |
| 5 | 5 | Print 5 |
Example 2: Step keyword
Sub PrintEvenNumbers()
Dim i As Long
For i = 2 To 10 Step 2
Debug.Print i
Next i
End Sub
Step 2 increases the counter by 2 each time.
A negative Step can be used to count backwards.
4. For Each...Next Loop
Use For Each...Next to process every item in a
collection, such as every cell in a range or every worksheet
in a workbook.
Example 3: Process cells in a range
Sub HighlightValues()
Dim cell As Range
For Each cell In ThisWorkbook.Worksheets("Sheet1").Range("A1:A5")
If IsNumeric(cell.Value) Then
If Len(cell.Value) > 0 Then
If cell.Value >= 50 Then
cell.Interior.Color = RGB(198, 239, 206)
End If
End If
End If
Next cell
End Sub
This macro checks cells A1:A5 and highlights numeric values greater than or equal to 50.
Sheet1. When looping through cells,
check for errors and non-numeric values before performing
numeric comparisons.
5. Do While...Loop
A Do While loop repeats as long as its condition
is True. When the condition is False at the beginning, the
loop body does not execute.
Syntax
Do While condition
' Statements
Loop
Example 4: Print numbers from 1 to 5
Sub DoWhileExample()
Dim i As Long
i = 1
Do While i <= 5
Debug.Print i
i = i + 1
Loop
End Sub
The counter must be updated. Otherwise, if the condition remains True, the loop may continue indefinitely.
6. Do Until...Loop
A Do Until loop repeats while the condition is
False and stops when the condition becomes True.
Syntax
Do Until condition
' Statements
Loop
Example 5: Print numbers until 5
Sub DoUntilExample()
Dim i As Long
i = 1
Do Until i > 5
Debug.Print i
i = i + 1
Loop
End Sub
Do While condition continues while the condition
is True. Do Until condition continues while the
condition is False.
7. Do...Loop While and Do...Loop Until
These forms check the condition at the end of the loop. Therefore, the statements inside the loop execute at least once.
Example 6: Do...Loop While
Sub PostTestWhile()
Dim i As Long
i = 1
Do
Debug.Print i
i = i + 1
Loop While i <= 5
End Sub
Example 7: Do...Loop Until
Sub PostTestUntil()
Dim i As Long
i = 1
Do
Debug.Print i
i = i + 1
Loop Until i > 5
End Sub
8. Exit For and Exit Do
Use Exit For to leave a For loop early and
Exit Do to leave a Do loop early.
Example 8: Stop when a value is found
Sub FindValue()
Dim i As Long
Dim target As Long
target = 7
For i = 1 To 10
If i = target Then
Debug.Print "Value found: " & i
Exit For
End If
Next i
End Sub
Once i becomes 7, the macro exits the loop
without checking the remaining values.
9. Nested Loops
A nested loop is a loop placed inside another loop. The inner loop completes its iterations for each iteration of the outer loop.
Example 9: Multiplication table
Sub MultiplicationTable()
Dim i As Long
Dim j As Long
For i = 1 To 3
For j = 1 To 3
Debug.Print i & " x " & j & " = " & (i * j)
Next j
Next i
End Sub
The outer loop selects the first number, and the inner loop multiplies it by 1, 2, and 3.
10. What Is a Dynamic Range?
A fixed range refers to a specific group of cells, such as
A2:A10. A dynamic range adjusts according to
the amount of data present in the worksheet.
Dynamic ranges are useful when the number of rows changes whenever new data is added to the worksheet.
11. Find the Last Used Row
One common method uses the End(xlUp) technique.
Starting from the bottom of a worksheet, VBA moves upward
to find the last non-empty cell in a specified column.
Syntax
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
ws.Rows.Countreturns the worksheet's row count.ws.Cells(ws.Rows.Count, "A")refers to the bottom cell in column A.End(xlUp)moves upward to the nearest non-empty cell..Rowreturns that cell's row number.
Example 10: Find the last row
Option Explicit
Sub FindLastRow()
Dim ws As Worksheet
Dim lastRow As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
If Application.WorksheetFunction.CountA(ws.Columns("A")) = 0 Then
MsgBox "Column A contains no data.", vbInformation
Exit Sub
End If
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
MsgBox "Last used row in column A: " & lastRow
End Sub
12. Use For...Next with a Dynamic Range
Once the last row is known, the loop can process every row between the first data row and the last used row.
Example 11: Calculate total price
Assume column B contains quantity, column C contains unit price, and column D should contain the total.
| A: Product | B: Quantity | C: Unit Price | D: Total |
|---|---|---|---|
| Mouse | 2 | 500 | 1000 |
| Keyboard | 3 | 750 | 2250 |
| Monitor | 1 | 8000 | 8000 |
Option Explicit
Sub CalculateProductTotals()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
If Application.WorksheetFunction.CountA(ws.Columns("A")) = 0 Then
MsgBox "No products found in column A.", vbInformation
Exit Sub
End If
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If lastRow < 2 Then
MsgBox "No product rows to process.", vbInformation
Exit Sub
End If
For i = 2 To lastRow
If IsNumeric(ws.Cells(i, "B").Value) And _
IsNumeric(ws.Cells(i, "C").Value) And _
Len(ws.Cells(i, "B").Value) > 0 And _
Len(ws.Cells(i, "C").Value) > 0 Then
ws.Cells(i, "D").Value = _
CDbl(ws.Cells(i, "B").Value) * _
CDbl(ws.Cells(i, "C").Value)
Else
ws.Cells(i, "D").Value = "Check Input"
End If
Next i
MsgBox "Product totals calculated.", vbInformation
End Sub
The macro automatically adjusts to the number of product rows, so new products can be added without changing the loop's ending row manually.
13. Practical Example: Fill Blank Cells
Suppose a worksheet contains missing department labels. You can use a loop to fill blank cells with a default value.
Example 12: Fill blank department cells
Option Explicit
Sub FillBlankDepartments()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
If Application.WorksheetFunction.CountA(ws.Columns("A")) = 0 Then
MsgBox "No employee data found.", vbInformation
Exit Sub
End If
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastRow
If Len(Trim$(CStr(ws.Cells(i, "B").Value))) = 0 Then
ws.Cells(i, "B").Value = "Not Assigned"
End If
Next i
MsgBox "Blank department cells have been checked.", vbInformation
End Sub
This macro assumes employee records are identified in column A and department values are stored in column B.
14. Use For Each with a Dynamic Range
You can combine the last-row technique with
For Each to process all populated data cells
in a calculated range.
Example 13: Highlight values above 1,000
Option Explicit
Sub HighlightLargeAmounts()
Dim ws As Worksheet
Dim lastRow As Long
Dim cell As Range
Set ws = ThisWorkbook.Worksheets("Sheet1")
If Application.WorksheetFunction.CountA(ws.Columns("C")) = 0 Then
MsgBox "No amounts found in column C.", vbInformation
Exit Sub
End If
lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
For Each cell In ws.Range("C2:C" & lastRow)
If Not IsError(cell.Value) Then
If Len(cell.Value) > 0 And IsNumeric(cell.Value) Then
If CDbl(cell.Value) > 1000 Then
cell.Interior.Color = RGB(255, 235, 156)
End If
End If
End If
Next cell
MsgBox "Highlighting completed.", vbInformation
End Sub
For Each is often convenient
when working directly with Range objects. Use an indexed
For...Next loop when you need to refer to several
columns in the current row.
15. Loop Through All Worksheets
A workbook can contain multiple worksheets. Use
For Each to perform the same operation on
every worksheet.
Example 14: Display worksheet names
Sub ListWorksheetNames()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Debug.Print ws.Name
Next ws
End Sub
Open the Immediate window in the VBA editor to see the worksheet names printed by this macro.
16. Improve Loop Performance
Loops over large worksheets can be slow if the macro repeatedly reads and writes individual cells. For larger datasets, consider reading data into an array, processing it in memory, and writing the results back in one operation.
Example 15: Process values using an array
Option Explicit
Sub CalculateTotalsWithArray()
Dim ws As Worksheet
Dim lastRow As Long
Dim inputData As Variant
Dim outputData() As Variant
Dim i As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
If Application.WorksheetFunction.CountA(ws.Columns("A")) = 0 Then
MsgBox "No product data found.", vbInformation
Exit Sub
End If
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If lastRow < 2 Then
MsgBox "No product rows to process.", vbInformation
Exit Sub
End If
' Read quantity and unit price into memory.
inputData = ws.Range("B2:C" & lastRow).Value2
ReDim outputData(1 To UBound(inputData, 1), 1 To 1)
For i = 1 To UBound(inputData, 1)
If Not IsError(inputData(i, 1)) And _
Not IsError(inputData(i, 2)) Then
If IsNumeric(inputData(i, 1)) And _
IsNumeric(inputData(i, 2)) And _
Len(CStr(inputData(i, 1))) > 0 And _
Len(CStr(inputData(i, 2))) > 0 Then
outputData(i, 1) = _
CDbl(inputData(i, 1)) * CDbl(inputData(i, 2))
Else
outputData(i, 1) = "Check Input"
End If
Else
outputData(i, 1) = "Check Input"
End If
Next i
' Write all results to column D in one operation.
ws.Range("D2:D" & lastRow).Value = outputData
MsgBox "Array calculation completed.", vbInformation
End Sub
17. Different Ways to Find the Last Row
| Method | Use | Consideration |
|---|---|---|
End(xlUp) |
Finds the last non-empty cell in a column. | Choose a column that reliably identifies the data rows. |
CurrentRegion |
Finds a connected data region around a starting cell. | Blank rows or columns can split the region. |
UsedRange |
Returns the worksheet's used range. | Formatting in otherwise empty cells can affect its size. |
Find |
Can search for the last cell containing data across a worksheet. | Search settings should be specified deliberately. |
Example 16: Find the last cell containing a value or formula
Option Explicit
Sub FindLastUsedRowAcrossSheet()
Dim ws As Worksheet
Dim lastCell As Range
Dim lastRow As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set lastCell = ws.Cells.Find( _
What:="*", _
After:=ws.Cells(1, 1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
If lastCell Is Nothing Then
MsgBox "Worksheet contains no values or formulas.", vbInformation
Exit Sub
End If
lastRow = lastCell.Row
MsgBox "Last used row: " & lastRow, vbInformation
End Sub
This searches across the worksheet instead of relying on one specific column. It looks for cells containing constants or formulas; formatting alone is not treated as a match.
18. Common Mistakes to Avoid
-
Forgetting to update the loop counter in a
Do WhileorDo Untilloop. - Creating an infinite loop because the stopping condition never becomes satisfied.
-
Using
Integerfor large row counters instead ofLong. - Assuming the last used row in column A is the last used row in every other column.
- Processing the header row accidentally instead of starting from the first data row.
- Performing calculations without validating blanks, errors, or non-numeric cell values.
-
Assuming
UsedRangealways identifies the exact data boundaries. - Writing over existing worksheet data without checking the output column first.
19. Practice Exercises
Complete these tasks in Excel VBA. Test your solutions with empty worksheets, blank cells, and different data sizes.
- Print numbers from 1 to 100 using
For...Next. - Print all even numbers between 1 and 50 using
Step. - Calculate the sum of numbers from 1 to 100.
- Use
For Eachto highlight values greater than 500. - Use
Do Whileto print numbers from 1 to 20. - Use
Do Untilto stop when a counter reaches 10. - Use
Exit Forto stop when a target number is found. - Find the last data row in column A.
- Calculate quantity multiplied by price for every product row.
- Fill empty department cells with a default value.
- Loop through all worksheets and print their names.
- Use a dynamic range to calculate employee bonuses.
- Rewrite a cell-by-cell calculation using an array.
- Find the last used row across the worksheet with
Find.
Important Points to Remember
- Use
For...Nextwhen the iteration count is known. - Use
For Each...Nextfor collections and cell ranges. - Use
Do Whilewhile a condition is True. - Use
Do Untiluntil a condition becomes True. - Use
Exit ForandExit Doto leave loops early. - Use
Longfor worksheet row counters. - Calculate the last row dynamically when data size changes.
- Validate cell values before processing them.
- Use arrays for efficient processing of large datasets.
Summary
Loops repeat instructions and eliminate repetitive code. VBA provides For...Next, For Each...Next, Do While, Do Until, and post-test Do loops. Dynamic ranges allow macros to adapt to changing worksheet sizes, while last-row techniques help determine how much data needs processing.
By combining loops, data validation, dynamic ranges, and arrays, you can build reliable and efficient Excel automation for reports, calculations, formatting, and data cleaning.