This tutorial will show you how to use

**FOR NEXT**loop in VBA.**What is loop ?**

It allows you to execute code repeatedly until a certain condition is reached.

**FOR NEXT**loop is as follows :

ForThe example is shown in the image below :< Counter Variable>=<Initial Value>To<End Value>

<Code that you want to repeat through loop>

Next <Counter Variable>

**Examples**

**1. Write series of numbers**

The following code writes values from 1 to 5 in column A :

Sub forloop()In the first loop counter, Range("A" & i) evaluates to Range("A"&1) i.e. Range("A1"). In the second loop counter, Range("A" & i) evaluates to Range("A"&2) i.e. Range("A2"). Then the range increments by 1 till fifth row.

Dim i As Integer

For i = 1 To 5

Range("A" & i).Value = i

Next i

End Sub

The output is shown in the image below :

**2. Multiply column C values by 2**

The data is shown in the image below :

Sub Dynamic()

Dim last As Double, i As Integer

With ActiveSheet

last = .Cells(.Rows.Count, "C").End(xlUp).Row - 2

End With

For i = 1 To last

Range("D" & i + 2).Formula = Range("C" & i + 2).Value * 2

Next i

End Sub

**Explanation :**

1. The following code identifies the last used row in column C. 2 is substracted from the code because column C contains blank row and header at the beginning of the column.

With ActiveSheet

last = .Cells(.Rows.Count, "C").End(xlUp).Row - 2

End With

2. In the first loop counter, Range("D" & i + 2) evaluates to Range("D"&1+2) i.e. Range("D3"). Then the range increments by 1 till last used row.With ActiveSheet

last = .Cells(.Rows.Count, "C").End(xlUp).Row - 2

End With

**3. Filling a column with consecutive numbers in consecutive cells**

You can increment counter variable by any value you like. The

**Step**keyword tells excel to increment counter variable by the specified value.

Sub forloopstep()The

Dim i As Integer

For i = 1 To 10 Step 2

Range("A" & i).Value = i

Next i

End Sub

**Step 2**code tells excel to

**increment**

**i**each time through the loop by 2.

Greetings,

ReplyDeleteI have a excel sheet for which i want a VBA code to merge 10 consecutive rows in a column and repeating it till the end of rows. So after every 10 rows the next 10 rows gets automatically merged.

i would be really thankful if any help on this task is provided.