How to Use For Next Loop in Excel VBA (10 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

Are you looking for ways to use the VBA For Next loop in Excel? Then this is the right place for you.

A loop is used when we need to run an operation repeatedly. Without performing the same operation manually every time in Excel, we can apply the VBA loop operation. Different loops exist in Excel VBA.

In the above image, you can see that we have created a twelve-times table using a For Next loop. In this article, we will discuss the use of VBA For Next loop in Excel in detail with proper examples.

Table of Contents hide

Download Practice Workbook

Introduction to VBA For Next Loop in Excel

How to Launch VBA Editor in Excel

For Next Loop in Excel VBA: 10 Suitable Examples

1. Use Simple For Next Loop to Add First 10 Positive Integers

2. Insert Values in Active Worksheet Using For Next Loop

3. Use Selection Property to Iterate Over Selected Cell Ranges Using For Next Loop in Excel

4. Apply For Next Loop in a User Defined Function in Excel VBA

5. Continue VBA For Next Loop to Create Twelve Times Table

6. Use Nested For Next Loop to Insert Values in Cell Range in Excel

7. Use Backwards For Next Loop to Iterate Over Dataset in Reverse Order

8. Apply For Next Loop with Condition in Excel VBA

9. Delete Blank Rows Using VBA Function with For Next Loop in Excel

10. Use For Next Loop with User Input Using InputBox in Excel

How to Exit For Loop in Excel VBA

Excel VBA Do While Loop: Alternative to For Next

Conclusion

Download Practice Workbook

To download the workbook click the link below

For Next Loop in VBA.xlsm

Introduction to VBA For Next Loop in Excel

Summary

The VBA For Loop is a loop that runs an operation a specific number of times. After running the loop a specific number of times, the program will stop automatically. For this reason, it is called a fixed loop.

Syntax

For counter = Start to End [Step]

Statements

Next counter

Explanation

Attribute	Description
counter	It is a numeric variable.
start	It indicates the starting value of the counter.
end	It defines the final value of the counter.
step	This is not a mandatory option. After each time running a code, the value of the counter changes. This changing amount depends on this attribute.
statements	This is also not mandatory. One or more statements may exist in a VBA code. The statements define the operations those needs to be performed in a loop.
next	This statement orders to run the counter again with the stepsize and runs until it reaches the ending point.

How to Launch VBA Editor in Excel

To write a VBA code in Excel, you have to launch the VBA editor. For launching this VBA editor, you must need the Developer tab, now if you don’t see the Developer tab then you have to enable the Developer tab.

You can insert your code into a module following the steps given below.

First, go to the Developer tab >> click on Visual Basic.

Now, the Microsoft Visual Basic Editor will open.
Lastly, to add a module, click on Insert >> select Module.

For Next Loop in Excel VBA: 10 Suitable Examples

We already discussed the attributes of the VBA For Next loop. Now, we will show some examples to understand the use of this loop in Excel VBA.

1. Use Simple For Next Loop to Add First 10 Positive Integers

Suppose you want to add the first 10 positive integers and show the resultant value in a MsgBox. You can do that easily by using a For Next loop in VBA instead of manually adding each value.

Here, we have shown you the dataset containing those 10 numbers.

Now, to add those values and show the sum value in MsgBox, copy the code given below into your module.

Sub Adding_Positive_Integers()
Dim Sum As Integer, i As Integer
Sum = 0
'Loop through first 10 positive integer
For i = 1 To 10
Sum = Sum + i
Next i
'Show Sum value in Msgbox
MsgBox Sum
End Sub

In the beginning, we set the value of Sum to 0. Then, we created a For Next loop to run 10 times, adding the current value of i to Sum on each iteration. Finally, we used a MsgBox to show the value of Sum.

Finally, if you run the code, you can see the sum value in MsgBox.

2. Insert Values in Active Worksheet Using For Next Loop

In this example, we will show you how you can insert values in your active worksheet using a For Next loop instead of inserting each value manually.

Here, we will insert the 1st 10 positive numbers into the active worksheet using For Next loop.

Use the following code in your module to do that.

Sub Insert_Values()
Dim i As Integer
For i = 1 To 10
'Using ActiveCell Property
ActiveCell.value = i
'Using Offset property to activate next cell
ActiveCell.Offset(1, 0).Activate
Next i
End Sub

In the above code, we created a loop from 1 to 10. Inside the loop, the value of i was used as the currently active cell using the ActiveCell property, and then we used the Offset property to move the active cell down one row so that the next value could be inserted into a new cell. Here, we did not mention ActiveSheet object as VBA by default assumes that the code should run in the active worksheet if no specific worksheet is mentioned.

Now, select the cell where you want to insert your first value.
Then, run the code to insert all the values in your active worksheet.

3. Use Selection Property to Iterate Over Selected Cell Ranges Using For Next Loop in Excel

Now, suppose you want to iterate over your selected cell range. You can do that by using the Selection property inside your For Next loop.

Here, we have a dataset containing some positive and negative numbers and we want to remove only the negative numbers from this dataset. Follow the given steps to do that on your own.

Copy the following code into your module.

Sub Selection_Property()
Dim i As Integer
Dim Row_Count As Integer
            'Using Selection Property to count number of selected rows
            Row_Count = Selection.Rows.Count
            'Iterating over selected cell range
            For i = 1 To Row_Count
            If Selection.Cells(i, 1).value < 0 Then
            'Using ClearContents method to clear contents
            Selection.Cells(i, 1).ClearContents
            End If
            Next i
End Sub

At first, we used the Selection property to count the number of rows selected. Next, we loop through each row and check if the value of the current cell is less than 0, then it will clear its content by using the ClearContent method.

After that, select your desired cell range.
Now, if you run the code, you will see that the negative values have been removed.

4. Apply For Next Loop in a User Defined Function in Excel VBA

You can also apply a For Next loop to create a user-defined function in Excel VBA if you need to do a repetitive task in that function.

Here, we have a dataset containing some alphanumeric values as Password. The numeric part of these strings is the corresponding ID No.

Now, we will create a user defined function to find the numeric part from the string using a For Next loop to loop through each character in the string.

To do that, copy the following code into your module.

Function NUMERIC_VALUE(value As Range)
Dim i As Integer
Dim num_value As Long
            'Using Len function to find length on the value
            'looping through each character of the string
            For i = 1 To Len(value)
            'Using IsNumeric function to find the numeric part
            If IsNumeric(Mid(value, i, 1)) Then
            num_value = num_value & Mid(value, i, 1)
            End If
            Next i
NUMERIC_VALUE = num_value
End Function

In the above code, we created a loop to iterate over the length of our string value using the LEN function. Then, we used the MID function to extract each character of the value string and the IsNumeric function to check whether the current character is a numeric digit.

After that, select Cell C5 and insert the following formula.

=NUMERIC_VALUE(B5)

Press Enter and drag down the Fill Handle tool to copy the formula.
Thus, you can create a user defined function using For Next loop in Excel.

5. Continue VBA For Next Loop to Create Twelve Times Table

If you want to loop through a specific range and change the value of increment or decrement in each step which is by default 1, you can do that by using the Step keyword.

In this example, we will show you how to create a twelve times table using the Step keyword in For Next loop in Excel VBA.

Use the following code in your module to do that.

Sub Create_Table()
Dim value As Byte
Dim row As Byte
For value = 12 To 144 Step 12
            row = value / 12
            Cells(row + 4, 2) = value
Next value
End Sub

Here, we used the For Next loop to loop through 12 to 144 and set the increment value in each step as 12 using the Step keyword.

Now, if you run the code, you will see that the twelve times table has been created into your active worksheet.

6. Use Nested For Next Loop to Insert Values in Cell Range in Excel

Previously, we showed you some examples using a single For Next loop in Excel. Here, we will apply some nested For Next loops, that will set the value of selected cells.

Suppose we want to set the value of the table of dimension 5×5. We will first do that by using For Next loop twice.

To do that, put the following code on the VBA window.

Sub Double_loop()
Dim Row_no As Integer, col_no As Integer
'Looping through Row 4 to Row 8
For Row_no = 4 To 8
'Looping through Column B to Column F
For col_no = 2 To 6
'Inserting values using Value property
Cells(Row_no, col_no).value = 5
Next col_no
Next Row_no
End Sub

In this code, we used the first loop to loop through each row and the second loop to loop through each column of the desired cell range.

Then, if you click on the run button, you will see value 5 is set on a 5×5 table. As we apply double for loop the code becomes able to set the value in the row and column both ways.

Now, if you want to change the values of each cell with an increment of 2, you can add another loop to this code.

Use the following code in your module to do that.

Sub Triple_loop()
Dim Row_no, col_no, value As Integer
value = 5
'Looping through Row 4 to Row 8
For Row_no = 4 To 8
'Looping through Column B to Column F
For col_no = 2 To 6
'Using Value Property to insert value
For value = 5 To value Step 2
Cells(Row_no, col_no).value = value
Next value
Next col_no
Next Row_no
End Sub

We added another loop in the code to increment the value with step 2 for each cell in the desired cell range.

Finally, run the code to get the 5X5 table like the image shown below values with step size 2.

7. Use Backwards For Next Loop to Iterate Over Dataset in Reverse Order

If you want to iterate over a dataset in reverse order, you can also do that by using the Step keyword in For Next loop in Excel. In this case, we have to set the value of Step as -1.

Here, we have a dataset containing the Name and Age of some students. Now, suppose we want to show the names of the students in this list in a MsgBox in reverse order.

Firstly, copy the following code into your module to do that.

Sub Reverse_Order()
Dim LastRow As Long
Dim firstRow As Long
Dim Name As Variant
' Set the lastRow variable to the last row of data in column B
LastRow = Range("B" & Rows.Count).End(xlUp).row
' Iterate through each row of data in reverse order
For firstRow = LastRow To 5 Step -1
' Get the value of the current cell in column B
Name = Cells(firstRow, 2).value
'Use MsgBox to show the value
MsgBox "Here we have information about: " & Name
Next firstRow
End Sub

Code Breakdown

In this code, we used the Range.End(xlUp) method to find the last non-empty cell in Column B and assign it as the LastRow variable.
Then we looped through the 5th row to LastRow in reverse order by setting the Step keyword as -1.
In the loop, we assigned the Names variable as the value of the current cell in Column B using the Cells method. Finally, we showed the value of the Name variable using Msgbox.

Next, run the code, to see the name of the students in reverse order.

8. Apply For Next Loop with Condition in Excel VBA

You can also add conditions inside your For Next loop using the IF statement in Excel VBA.

Now, suppose you want to highlight the cells where the Age of the student is equal to 12 in the dataset we showed in Example 7. To do that, you have to use an IF statement in the For Next loop.

Firstly, insert the following code into your module.

Sub Color_Cells()
Dim LastRow As Long
Dim x As Long
'Finding Last Row
LastRow = Cells(Rows.Count, 2).End(xlUp).row
'Clearing any Color already present in Column C
Range("C5", Cells(LastRow, 3)).Interior.Color = xlNone
For x = 5 To LastRow
'Applying condition to change cell color
If Cells(x, 3) = 12 Then Cells(x, 3).Interior.ColorIndex = 15
Next x
End Sub

Code Breakdown

In the above code, we used the Range.End(xlUp) method to find the last non-empty cell in Column B and assign it as the LastRow variable.
Then, we cleared any color present in Cell C5 to the last row in Column C by using xlNone as the background color of these cells.
Next, we used the For Next loop to iterate through the rows of data from the 5th row to the LastRow.
In the loop, we used the IF statement, to check if the value of the current cell in Column C is equal to 12. If the condition in the IF statement is True, the cell color will change. Here, we used the Interior.ColorIndex property to change the color and set it as 15.

Finally, click on the Run button to change the color of the cells containing the value 12.

9. Delete Blank Rows Using VBA Function with For Next Loop in Excel

You can also use different VBA functions inside your For Next loop according to your need.

Suppose we have a dataset containing some blank rows and we want to delete those using a VBA code instead of removing them one by one manually.

To do that, copy the following code inside your module.

Sub Deleting_Blank_Rows()
Dim No_of_Rows As Long, x As Long
Dim Current_Row As Range
'Counting number of rows
No_of_Rows = Cells(Rows.Count, 2).End(xlUp).row
For x = 5 To No_of_Rows
Set Current_Row = Cells(x, 1).EntireRow
'Using CountA function which counts non empty cells only
If WorksheetFunction.CountA(Current_Row) = 0 Then Current_Row.Delete
Next x
End Sub

Here, we used the CountA function to count the number of non-empty cells in the current row. If the resultant value of the function is 0, then the Current_Row is deleted using the Delete method.

Run the code, to delete all the blank rows inside your dataset.

10. Use For Next Loop with User Input Using InputBox in Excel

Using a For Next loop you can easily iterate over the number of times the user wants using an InputBox in Excel.

Here, we will create a times table according to the user input using a For Next loop in Excel.

Use the following code to do that.

Sub Using_Input()
Dim i As Long
Dim j As Long
Dim Input_Value As Long
'Using InputBox
Input_Value = InputBox _
("Insert a number up to which you would like times table to be created")
'Looping to each cell according to user input
For i = 1 To Input_Value
         For j = 1 To Input_Value
                  Cells(i + 4, j + 1) = i * j
         Next j
Next i
End Sub

Now, if you run the code an InputBox will open. Firstly, we entered 4 in the box and it created a 4 times table. Next, as we entered 10 in the box, it created a 10 times table.

How to Exit For Loop in Excel VBA

In this section, we will show the use of the VBA Exit For in Excel. VBA For loop stops when the counter reaches the assigned maximum. But you can add an Exit For statement to exit a For Loop before the assigned maximum count is reached when meeting other criteria.

We will insert positive integers in the active worksheet starting from 1 and then exit the loop when the value is 5.

Use the following code in your module to do that.

Sub Number_to_Limit()
Dim number As Integer
For number = 1 To 10
ActiveCell.value = number
ActiveCell.Offset(1, 0).Activate
If number = 5 Then
'using Exit For statement
Exit For
End If
Next number
'using Msgbox
MsgBox "Break"
End Sub

Here, we used the Exit For statement to exit the loop when the value of the number variable is equal to 5 and show Break as a prompt in a MsgBox.

Now, run the code.

Excel VBA Do While Loop: Alternative to For Next

The operation of the Do While Loop is quite similar to the For Next loop. This Do While loop is used when a repeated process is needed. It runs the operation until the given condition is true. Here is an example of the Do While loop to input 1st 10 integers numbers in Excel.

Copy this code into your module to use it.

Sub Using_Do_While_Loop()
'using Do While loop
Do While Row_no < 10
Row_no = Row_no + 1
ActiveCell.value = Row_no
ActiveCell.Offset(1, 0).Activate
Loop
End Sub

In the above code, we used the Do While loop instead of For Next loop to continue the loop while the value of Row_no variable is less than 10.

Select the cell where you want to insert the first value and run the code.

Conclusion

In this article, we discussed For Next Loop VBA in Excel. We explained with examples. We also added some other VBA related to this For Next loop. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.

How to Use For Each Loop in Excel VBA (3 Suitable Examples)

Use Do Until Loop in Excel VBA (with 2 Examples)