Do While Loop Excel VBA


The Do While loop is a useful tool in Excel VBA used to repeat a set of steps as long as the statement is TRUE. When the statement is true we want the loop to stop and the loop naturally comes to an end. So in phrasing the loop the condition is set at the start of the loop. The Do While Loops condition is then checked with each iteration of the loop.

It is the opposite of the Do Until Loop in VBA - where the true condition is where the code stops.

The following are some examples of Do Loops in a practical settings:

Sub DoWhileEg1()
Dim i As Integer
i = 1

Do While i <= 10
Range("A" & i) = i
i = i + 1
Loop
End Sub

The above loop will place the following numbers in Column A.

Do Loop Excel VBA

 The above is the iteration from 1 to 10. It is what the loop was asked to do. Produce a number from 1 to 10.

The i = i + 1 is the heart of the iteration. It allows the loop to increment 1 cell each time.

Loop in VBA

The above is how the code should look in the VBE editor.


The following is another example of how to use the Do While Looping construct. In this Do While Looping example the above cells in column A will be doubled in column B and the code will only run while the cells in column A are not blank. In this example, the IsEmpty function is evoked with a NOT statement, which says run the procedure while the cells are NOT EMPTY.

Sub DoWhileEg2()
Dim i As Integer
i = 1

Do While Not IsEmpty(Cells(i, 1))
Range("B" & i) = Cells(i, 1) * 2
i = i + 1
Loop
End Sub

The following is how the cells in column B will look after the VBA code has run.

Loop3.PNG

The above is an example of how the code might look with the numbers doubling.

Loop vba Excel

The above is how the code should look in the VBA editor.

The following is a file with the practical examples described above. Enjoy.

Do While Loop.xlsm