VBA Loops

Excel VBA For Loop – Complete Guide

Welcome to the complete Excel VBA For Loop tutorial. In this lesson you will learn how to repeat VBA code automatically, work with Excel cells and ranges, create nested loops, use For Each, and build practical Excel automation programs.

For Loop Course Contents

  1. What is a Loop?
  2. Why do we use For Loop?
  3. Types of For Loop in VBA
  4. Basic For...Next Loop
  5. Understanding Loop Counter
  6. Using Step
  7. Negative Step
  8. Exit For
  9. Nested For Loops
  10. For Each...Next
  11. Loop Through Cells
  12. Loop Through Rows
  13. Loop Through Columns
  14. Loop Through Worksheets
  15. Loop Through Arrays
  16. Dynamic Last Row
  17. If Statement Inside For Loop
  18. Practical Excel Projects
  19. Common Mistakes
  20. Practice Quiz

1. What is a Loop?

A loop is used when we want VBA to execute the same block of code multiple times.

For example, suppose Excel contains numbers from 1 to 100 and you want to print every number.

Without a loop, you would have to write the same instruction many times.

A loop allows VBA to repeat the instruction automatically.

Example: 
Sub BasicLoop()

    Dim i As Long

    For i = 1 To 10
        Debug.Print i
    Next i

End Sub

The above code prints numbers from 1 to 10 in the Immediate Window.

2. Why Do We Use For Loop?

For Loop is extremely useful in Excel VBA because Excel contains large amounts of rows, columns and cells.

For example:

  • Read 10,000 rows
  • Check every customer
  • Find blank cells
  • Format rows
  • Copy data
  • Delete unwanted records
  • Calculate values
  • Process worksheets
  • Process arrays

3. Types of For Loop in VBA

There are two main forms of For Loop that you should learn in VBA.

Loop Purpose
For...Next Repeat code using a numeric counter
For Each...Next Loop through objects/items in a collection

Nested loops are not a separate type of loop. A nested loop simply means placing one For Loop inside another For Loop.

4. Basic For...Next Loop

The basic syntax is:

For counter = start To end
    'Code
Next counter

Example:

Sub Example1()

    Dim i As Long

    For i = 1 To 5

        Debug.Print i

    Next i

End Sub

The output is:

1
2
3
4
5

5. Understanding the Counter

The variable i is called the loop counter. 

For i = 1 To 5

Here:

  • i = counter
  • 1 = starting value
  • 5 = ending value

The loop automatically increases the counter by 1 when no Step is specified.

6. Using Step

The Step keyword controls how much the counter changes during each iteration. 

For i = 1 To 10 Step 2

    Debug.Print i

Next i

Output:

1
3
5
7
9

The counter increases by 2 each time.

Step 5 Example

For i = 0 To 20 Step 5

    Debug.Print i

Next i

Output:

0
5
10
15
20

7. Negative Step

You can also decrease the counter using a negative Step. 

For i = 10 To 1 Step -1

    Debug.Print i

Next i

Output:

10
9
8
7
6
5
4
3
2
1
Important:

When the starting value is greater than the ending value, use a negative Step.

8. Exit For

Sometimes you don't want to complete the entire loop. You can use Exit For to stop the loop. 

Sub ExitExample()

    Dim i As Long

    For i = 1 To 100

        If i = 10 Then
            Exit For
        End If

        Debug.Print i

    Next i

End Sub

The loop stops when i reaches 10.

9. Nested For Loop

A nested loop means one loop is placed inside another loop. 

Sub NestedLoop()

    Dim i As Long
    Dim j As Long

    For i = 1 To 3

        For j = 1 To 3

            Debug.Print i, j

        Next j

    Next i

End Sub

The inner loop completes all its iterations before the outer loop moves to its next value.

Practical Excel Example – Multiplication Table

Sub MultiplicationTable()

    Dim i As Long
    Dim j As Long

    For i = 1 To 10

        For j = 1 To 10

            Cells(i, j).Value = i * j

        Next j

    Next i

End Sub

10. For Each...Next Loop

For Each is used when you want to process every object inside a collection.

Example:

Sub LoopWorksheets()

    Dim ws As Worksheet

    For Each ws In Worksheets

        Debug.Print ws.Name

    Next ws

End Sub

This code prints the name of every worksheet in the workbook.

11. Loop Through Excel Cells

One of the most important uses of For Each in Excel VBA is looping through cells. 

Sub LoopCells()

    Dim cell As Range

    For Each cell In Range("A1:A10")

        Debug.Print cell.Value

    Next cell

End Sub

Highlight Cells Greater Than 100

Sub CheckValues()

    Dim cell As Range

    For Each cell In Range("A1:A100")

        If cell.Value > 100 Then

            cell.Font.Bold = True

        End If

    Next cell

End Sub

12. Loop Through Rows

You can use a numeric For Loop to process rows. 

Sub LoopRows()

    Dim i As Long

    For i = 2 To 100

        Cells(i, 1).Value = Cells(i, 2).Value * 10

    Next i

End Sub

Here column B is multiplied by 10 and the result is placed in column A.

13. Loop Through Columns

Sub LoopColumns()

    Dim i As Long

    For i = 1 To 10

        Cells(1, i).Value = i

    Next i

End Sub

This writes numbers 1 to 10 across row 1.

14. Loop Through Worksheets

Sub SheetLoop()

    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets

        Debug.Print ws.Name

    Next ws

End Sub

Rename Worksheets

Sub RenameSheets()

    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets

        ws.Name = "Sheet_" & ws.Index

    Next ws

End Sub

15. Loop Through Arrays

Sub ArrayLoop()

    Dim arr As Variant
    Dim i As Long

    arr = Array("Excel", "VBA", "SQL", "Python")

    For i = LBound(arr) To UBound(arr)

        Debug.Print arr(i)

    Next i

End Sub

LBound returns the lower boundary of the array and UBound returns the upper boundary.

16. Dynamic Last Row

In real Excel automation, you usually don't know how many rows the worksheet contains.

Instead of writing: 

For i = 2 To 100

you can find the last row dynamically. 

Sub DynamicLoop()

    Dim lastRow As Long
    Dim i As Long

    lastRow = Cells(Rows.Count, 1).End(xlUp).Row

    For i = 2 To lastRow

        Debug.Print Cells(i, 1).Value

    Next i

End Sub
Real-world VBA tip:

Dynamic last-row detection is one of the most important techniques for professional Excel VBA automation.

17. If Statement Inside For Loop

You will frequently combine For with If...Then. 

Sub FindSales()

    Dim i As Long
    Dim lastRow As Long

    lastRow = Cells(Rows.Count, 1).End(xlUp).Row

    For i = 2 To lastRow

        If Cells(i, 2).Value > 50000 Then

            Cells(i, 3).Value = "High Sales"

        Else

            Cells(i, 3).Value = "Normal Sales"

        End If

    Next i

End Sub

18. Practical Project – Find Blank Cells

Sub FindBlankCells()

    Dim cell As Range

    For Each cell In Range("A2:A100")

        If cell.Value = "" Then

            cell.Interior.Color = vbYellow

        End If

    Next cell

End Sub

19. Practical Project – Generate Serial Numbers

Sub GenerateSerialNumber()

    Dim i As Long
    Dim lastRow As Long

    lastRow = Cells(Rows.Count, 2).End(xlUp).Row

    For i = 2 To lastRow

        Cells(i, 1).Value = i - 1

    Next i

End Sub

20. Practical Project – Find Maximum Value

Sub FindMaximum()

    Dim i As Long
    Dim maxValue As Double

    maxValue = Cells(2, 1).Value

    For i = 3 To 100

        If Cells(i, 1).Value > maxValue Then

            maxValue = Cells(i, 1).Value

        End If

    Next i

    MsgBox "Maximum Value = " & maxValue

End Sub

21. Common For Loop Mistakes

Mistake 1 – Forgetting Next

For i = 1 To 10
    Debug.Print i

'Missing Next i

Always close the loop with Next.

Mistake 2 – Wrong Step

For i = 10 To 1
    Debug.Print i
Next i

This does not count downward because the default Step is +1. Use: 

For i = 10 To 1 Step -1
    Debug.Print i
Next i

Mistake 3 – Using a Fixed Last Row

For i = 2 To 100

If your data contains 5,000 rows, this will not process all data. Use dynamic last-row detection when appropriate.

22. VBA For Loop Performance

For small amounts of data, a normal loop is usually sufficient. For large Excel datasets, however, repeatedly reading and writing individual cells can become slow.

For professional VBA automation, you should eventually learn:

  • Arrays
  • Variant arrays
  • Application.ScreenUpdating
  • Application.Calculation
  • Bulk range operations

23. Practical Exercises

Exercise 1

Write a VBA program that prints numbers from 1 to 100.

Exercise 2

Print only even numbers from 2 to 100.

Exercise 3

Print numbers from 100 down to 1.

Exercise 4

Write numbers 1 to 50 into cells A1:A50.

Exercise 5

Read column A and write "PASS" in column B when the value is greater than or equal to 50.

Exercise 6

Find all blank cells in A2:A500 and highlight them.

Exercise 7

Loop through all worksheets and display their names.

Exercise 8

Create a multiplication table from 1 to 10 using nested loops.

Exercise 9

Find the largest number in column A.

Exercise 10

Find the total of all numeric values in column A using a For Loop.

24. For Loop Practical Quiz

Question 1:
What is the purpose of a For...Next loop?

A. To repeat code

B. To delete VBA

C. To create a worksheet

D. To close Excel

Answer: A — A For...Next loop repeats a block of VBA code.

Question 2:
What is the output of the following code? 

For i = 1 To 5
    Debug.Print i
Next i

A. 0 to 5

B. 1 to 5

C. 1 to 4

D. 5 to 1

Answer: B — The loop starts at 1 and ends at 5.

Question 3:
What does Step 2 do? 

For i = 1 To 10 Step 2

A. Decreases by 2

B. Increases by 1

C. Increases by 2

D. Stops the loop

Answer: C — Step 2 increases the counter by 2 after each iteration.

Question 4:
Which statement stops a For loop immediately?

A. Stop For

B. Exit For

C. End For

D. Break For

Answer: B — Exit For terminates the current For loop.

Question 5:
Which loop is suitable for processing every worksheet?

A. For Each

B. If Each

C. Select Each

D. Loop Sheet

Answer: A — For Each is commonly used to process every worksheet in a Worksheets collection.

Question 6:
Which code counts from 10 down to 1?

A. For i = 10 To 1

B. For i = 10 To 1 Step -1

C. For i = 1 To 10 Step -1

D. For i = 1 DownTo 10

Answer: B — A negative Step is required to decrease the counter.

Question 7:
What is a nested loop?

A. A loop with no Next statement

B. A loop inside another loop

C. A loop that never runs

D. A loop without a counter

Answer: B — A nested loop is a loop placed inside another loop.

Question 8:
Which code is appropriate for looping through cells A1:A10?

A. For Each cell In Range("A1:A10")

B. For Each cell In Worksheet

C. For Cell A1:A10

D. Loop Range A1:A10

Answer: A — A Range can be enumerated using For Each.

Question 9:
Which function is commonly used to find the last used row in column A?

A. Rows.Last

B. LastRow()

C. Cells(Rows.Count, 1).End(xlUp).Row

D. Range.LastRow

Answer: C — This is a common VBA pattern for finding the last non-empty cell in column A.

Question 10:
What happens when this code executes? 

For i = 1 To 100

    If i = 10 Then
        Exit For
    End If

Next i

A. The loop runs to 100

B. The loop stops when i reaches 10

C. The loop starts at 10

D. The loop runs forever

Answer: B — Exit For terminates the loop when the condition becomes true.

25. For Loop – Final Summary

Concept Example
Basic loop For i = 1 To 10
Step For i = 1 To 10 Step 2
Reverse loop For i = 10 To 1 Step -1
Exit loop Exit For
Nested loop For inside another For
Object loop For Each ws In Worksheets
Cell loop For Each cell In Range(...)
Array loop For i = LBound(arr) To UBound(arr)

Next Lesson: After mastering For Loop, the next important VBA topic is Do Loop – Do While, Do Until, Loop While and Loop Until.

Post a Comment

0 Comments