30 Advanced Excel Tips for 2026

Excel Champs / VBA / VBA FOR LOOP (For Next, For Each) – The Guide + Examples
By Puneet Gogia (Microsoft MVP)

VBA FOR LOOP (For Next, For Each) – The Guide + Examples

30+ Advanced Excel Tips for 2026

No spam. Only Excel Stuff. God Promise. 😊

Use For … Next to repeat code a set number of times. The counter goes up by 1 on each pass:

Sub NumberRows()
    Dim i As Long
    For i = 1 To 10
        Cells(i, 1).Value = i
    Next i
End Sub

Add Step to change the increment. For i = 1 To 10 Step 2 runs for 1, 3, 5, 7 and 9, and For i = 10 To 1 Step -1 counts down.

To work through every item in a collection, use For Each, which needs no counter: For Each ws In ThisWorkbook.Worksheets for every sheet, or For Each c In Range("A2:A100") for every cell. Close it with Next ws or Next c.

Leave a loop early with Exit For, for example If Cells(i, 1).Value = "" Then Exit For. The start and end values are read once when the loop begins, so changing them inside the loop does not change how many times it runs. After a full run, the counter is one step past the end value, so i is 11 after For i = 1 To 10.

When the loop deletes rows, count down with Step -1. Counting up skips the row that moves into the deleted one’s place. Declare row counters As Long, because Integer stops at 32,767 with an “Overflow” error.

Key Points

  • VBA For Loops are fixed types of loops.
  • It is not possible to create any of these loops while recording a macro; you need to write them in the Visual Basic Editor.

For Next Loop

VBA FOR NEXT is a fixed loop that uses a counter to run iterations. In simple words, you need to specify the number of times you want to run the loop, and once the loop reaches that count, it will stop automatically. That’s why it is a fixed loop and most popular among VBA developers.

Syntax

Following is the syntax for the VBA For Next Loop:

For counter = Start To End [Step stepsize]
[statements]
Next [counter]
for-conter-start-to-end
  1. Counter: It’s a variable that stores the count of the repetition and increases its value with each repetition. For example, if the loop starts at 1 and is in its fifth repetition, the value of the counter will be five, and once the loop executes the statement for the fifth iteration, the value of the counter changes to six.
  2. Start: It is a numeric value (or a variable) that sets the starting value for the counter.
  3. End: It is a numeric value (or a variable) that sets the ending value for the counter. VBA reads it only once, when the loop starts.
  4. Step Size: This value specifies how much the counter increases or decreases with each repetition. By default, the step size is one, but you can also use any positive or negative value.
  5. Statement: It’s a set of code that you want to execute in each iteration of the For Next loop.
  6. Next: It’s the end statement for one iteration of the loop and uses the counter value to tell VBA to repeat the loop and once the counter reaches the end value it ends the loop.

Example to Understand For Next Loop

To understand how to use the For Next loop, let’s write a simple code to add serial numbers in a column.

example-to-understand-for-next-loop

To use For Next Loop, you can use the following steps:

  1. First, you need to declare a variable that you can use to store the counter value.
    declare-a-variable
  2. Next, we need to assign the counter value to the variable.
    assign-the-counter-value
  3. Now it’s time to write code for the loop, so in the first line, you need to use the counter variable and define the starting and the ending value. Here in this code, you have the variable “i” (which is 10 at this point) as the ending value.
    write-code-for-loop
  4. From here, you need to write code that can add the serial numbers into the active cell and then move to the next cell. As the counter value increases in each iteration, you can use it to enter the serial number into the active cell and after that, you can use the offset to move the selection to the next cell downwards.
    code-that-can-add-serial-number
  5. In the end, use the keyword Next and the counter variable to end the code for the loop.
Sub AddSerialNumbers()
Dim i As Integer
i = 10 

    For i = 1 To i 
  
        ActiveCell.Value = i       
        ActiveCell.Offset(1, 0).Activate  
 
    Next i

End Sub

You can see in the below snapshot that when I run this code, it starts from the selected cell by entering a serial number and then moves to the next cell with each iteration.

vba-for-next-loop-example-1

As you know, For Next is a fixed loop, and in this code, when you assign a numeric value, it tells VBA to run the loop that number of times. Using the same variable as the counter and the end value works, as VBA reads the end value once before the loop starts, but a separate variable (like For i = 1 To n) is easier to read.

And you can see the below snapshot that I have taken in the second iteration of the loop where the value of the “i” counter variable is two.

assign-a-numeric-value

The point I’m trying to make is the counting variable changes its value in each iteration and enters that value in the active cell and then Offset activates the next cell downward, which completes one iteration.

In the end, the “Next” keyword tells VBA to go back to the first line of the loop and repeat it. As you have seen in the syntax of For Next, you can also use a step value in the loop to make the counter work according to that.

So if you use two as a step value, the code would be something like the below.

Sub AddSerialNumbers()

Dim i As Integer
i = 10 

    For i = 1 To i Step 2  

        ActiveCell.Value = i
        ActiveCell.Offset(1, 0).Activate 
  
    Next i

End Sub

And when you run this code, it will do something like the below snapshot.

when-you-run-a-code-snapshot

When you use a step value (like Step 2, which you have used in the above code), the counter goes up by 2 each time, so the loop runs only five times, and as you can see, you have got five numbers (1, 3, 5, 7 and 9) with a difference of two between each number.

Note: In the above example, you have used “i” as the counter variable, but you can use a different name as well according to your convenience.

For Each Next Loop

VBA FOR EACH NEXT is a fixed loop that can loop through all the objects in a collection. In the For Each Next, you don’t need to specify the count of iterations. Instead, you can specify a collection of objects, and it will be able to loop through all those objects one by one.

Syntax

Following is the syntax for the VBA For Each Next Loop:

For Each Object In Collection
[statements]
Next [object]
syntax-for-the-vba-for-each-next-loop
  1. Object: It represents the one object in the collection of objects in which you are looping. With each iteration, it moves from one object to the next.
  2. Collection: It’s a collection of objects in which you want to loop.
  3. Statement: Line(s) of code that you want to execute in each loop through all the objects in the collection.
  4. Next: It’s the end statement for one iteration of the loop and tells VBA to move to the next object in the collection.

Example to Understand the For Each Next Loop

To understand For Each Next loop, let’s write a code to hide all the sheets other than the active worksheet. This code will check each worksheet’s name and match it with the active worksheet’s name and hide it if it doesn’t match.

To use For Each Next loop, you can use the following steps:

  1. First, you need to declare a variable to use as a worksheet.
    code-to-hide-the-all-sheets
  2. After that, use the “For Each” keyword, and then the “mySheet” variable for the sheet, and use “ActiveWorkbook.Worksheets” for the collection of worksheets from the active workbook.
    for-each-mysheet-active-workbook-worsheets
  3. Next, you need to use VBA IF Statement to test if the name of the sheet that is currently in that loop iteration is NOT EQUAL to the active worksheet, and if this condition is true, then hide the sheet that is now in the loop iteration.
    vba-if-statement-to-test
  4. In the end, you have the NEXT keyword and the “mySheet” variable to end the code for the loop.
    next-keyword-and-mysheet

Final Code:

Sub Hide_Other_Sheets()
 
Dim mySheet As Worksheet
 
For Each mySheet In ActiveWorkbook.Worksheets

    If mySheet.Name <> ActiveSheet.Name Then

        mySheet.Visible = False

    End If

Next mySheet

End Sub

In this way, all the worksheets except the active one will be hidden.

Loop Backwards with a Negative Step

With a negative step, the counter goes down. This is the right way to delete rows in a loop: if you go from top to bottom, every deleted row moves the next row up, and the loop skips it. The following code deletes the empty rows from 1 to 100, starting from the bottom:

Sub delete_empty_rows()
Dim i As Long
For i = 100 To 1 Step -1
If WorksheetFunction.CountA(Rows(i)) = 0 Then
Rows(i).Delete
End If
Next i
End Sub

Exit a For Loop Early

Use Exit For to stop the loop as soon as you find what you’re looking for. The following code looks for the first empty cell in column A and stops there:

Sub find_first_empty_cell()
Dim i As Long
For i = 1 To 1000
If IsEmpty(Cells(i, 1).Value) Then
MsgBox "The first empty cell is A" & i
Exit For
End If
Next i
End Sub

Nested For Loops

You can put one loop inside another. The outer loop goes through the rows and the inner loop through the columns, so the following code fills A1:C5 with the row number times the column number:

Sub nested_loops()
Dim r As Long, c As Long
For r = 1 To 5
For c = 1 To 3
Cells(r, c).Value = r * c
Next c
Next r
End Sub

Each loop needs its own counter variable and its own Next. If one Next is missing, VBA shows the “For without Next” compile error.

Frequently Asked Questions

What is the difference between For Next and For Each Next?

For Next runs a set number of times with a counter. For Each Next runs once for each object in a collection, like each cell in a range or each sheet in a workbook, without a counter.

Should the counter be Integer or Long?

Use Long. An Integer can only go up to 32,767, so a loop through the rows of a large sheet stops with an Overflow error (Error 6).

How do I stop a loop that runs forever?

Press Esc or Ctrl + Break while the code runs, and then click End. A For loop stops on its own, but changing the counter inside the loop can keep it running.

Puneet Gogia

Written by

Puneet Gogia

Microsoft MVP — Excel · Founder, Excel Champs

Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.

3M+ readers helped
1,000+ tutorials written
Since 2015 teaching Excel