How to Use Do Until and Do While Loops in VBA: Step-by-Step

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Like for loops, do until and do while loops are powerful concepts that you’ll find in most programming languages.

They both belong to the “do loop” category in Excel. So, when you hear that reference, someone means either the do until loop or do while loop.

Both loops do a specified task inside the loop again and again until the loop ends♻️

Let me show you the do while and do until loops with some practical examples.

And I’ll pinpoint the differences between the two, so you know which loop in VBA that’s best for you.

If you want to tag along as you read this guide, please download the sample workbook here.

Using do loops in Excel VBA

Before we get into the specific syntax of the VBA do until loop, let’s talk about exactly what’s happening here.

Here’s a real-world example: if you have a spreadsheet full of first and last names in columns A and B, you might combine them, one by one, into column C. You’ll do this until all of the names have been combined.

In essence, after each combination, you ask yourself: “Are all of the names combined?” If the answer is no, you keep going. As soon as the answer is yes, you stop.

The example spreadsheet linked above has a series of first names in column A and last names in column B.

Let’s write a loop in VBA to combine them into full names in column C👌🏼

Take a look at the below code. That’s the VBA do until loop that combines the content of cells.

The loop starts with the “Do Until” statement. Then, it’s specified when the loop should end. Then the loop executes all the actions, then the next loop runs, then it combines cells again, moves to the next loop, etc.

Sub combineNamesUntil()
i = 2
Do Until IsEmpty(Cells(i, 1))
Cells(i, 3).Value = Cells(i, 1) & " " & Cells(i, 2)
i = i +1
Loop
End Sub

The first code line establishes i as a counter variable. We’ll come to its function in a moment.

The second line starts the loop. A do until loop needs a statement that resolves to TRUE or FALSE⚙️

In this case, we’re using IsEmpty, which checks a specific cell to see if it’s empty; if that cell contains a value, it returns FALSE. If it doesn’t contain a value, it returns TRUE.

A do until loop keeps running until it gets a TRUE back from this statement.

The next code line is the command we want to run.

In our case, we’re setting the cell in row i and column 3 (represented as (i, 3)) equal to the value of the cells in (i, 1) and (i, 2) separated by a space.

As i increases, so does the row we’re looking at. That’s the function of the counter variable in this script.

The next line is very important; it raises the counter variable by one. Without this line, Excel will continue looking at the same row forever.

Finally, we tell Excel that the loop is over with “Loop.”

Here’s what happens when we run the loop on the first sheet in the example workbook:

As you can see, the loop worked perfectly.

Most importantly, as soon as it detected a blank cell in column A, it stopped running.

Using do while loops in Excel VBA

A do while loop is almost exactly the same as a do until loop – there’s just one crucial difference.

This type of loop runs until the statement at the beginning resolves to FALSE.

It’s the opposite of do until in this manner, but everything else is the same.

Here’s how we’d write the same loop as above as a do while:

Sub combineNamesWhile()
 i = 2
 Do While Not IsEmpty(Cells(i, 1))
   Cells(i, 3).Value = Cells(i, 1) & " " & Cells(i, 2)
   i = i +1
   Loop
End Sub

As you can see from the above code, the only differences are to change “Do Until” to “Do While” and to enter “Not” in front of IsEmpty.

That “Not” is important. Because we’re checking for the TRUE/FALSE value from IsEmpty, we need to invert it to keep it working in the same way as the do until VBA loop.

If you run this loop on the example spreadsheet, you’ll see that it gets the same result😎

Let’s try something a little different with this loop. Take a look at the below code:

Sub combineNamesWhile()
 i = 2
 Do While Not IsEmpty(Cells(i, 1)) OR Not IsEmpty(Cells(i, 2))
   If IsEmpty(Cells(i, 1)) Then
     Cells(i, 3).Value = Cells(i, 2)
   ElseIf IsEmpty(Cells(i, 2)) Then
     Cells(i, 3).Value = Cells(i, 1)
   Else
     Cells(i, 3).Value = Cells(i, 1) & " " & Cells(i, 2)
   EndIf
   i = i +1
   Loop
End Sub

The main difference with this loop is that it has two conditions instead of just one. Here’s that clause:

Not IsEmpty(Cells(i, 1)) OR Not IsEmpty(Cells(i, 2))

In essence, the above code says that as long as (i, 1) or (i, 2) contains a value, the rest of the VBA loop will run. In the above example, It only stops when both cells are empty.

This lets us deal with rows that have a single name instead of two. Run the loop on the second sheet in the example workbook to see what happens:

Then, open the Visual Basic editor and step through the loop one step at a time by pressing F8.

See if you can work out how the conditionals work and why this particular version of the script is better for working with both first/last and single names.

Comparison: VBA do until loop vs do while loop

A do-while loop will keep running the instructions over and over, doing the same task again and again while a certain condition is true.

Which is quite similar to all VBA loops.

But…’

Once the condition is no longer true, the Excel VBA loop will stop. It’s like a runner who keeps running until they are tired and can’t run anymore🏃🏼‍♀️

The loop keeps going while the condition is true, just like the runner keeps running while they have energy.

On the other hand, a do-until loop is a little different. It will keep running the instructions until a specific condition is true.

It’s like a runner who keeps running until they cross the finish line. The loop keeps going until the condition is true, just like the runner keeps running until they cross the finish line.

Do you see the differences in these 2 examples?👀

So, in summary, a do-while loop keeps going while a certain condition is true, and a do-until loop keeps going until a certain condition is true. They’re both useful in different situations, and it’s important to understand how they work to use them effectively in programming.

That’s it – Now what?

In conclusion, do until and do while loops are powerful concepts that you can use to automate repetitive tasks in VBA.

Both loops can execute a specified task again and again until a condition is met. However, the main difference between them is the condition that controls the loop’s execution.

A do until loop in VBA runs until the condition is TRUE, while a do while loop runs until the condition is FALSE.

It’s essential to choose the right loop for the task at hand to avoid logical errors and optimize your code’s efficiency💡

By mastering the concepts of do until and do while loops, you can power up Excel and increase your productivity.

Loops are just one part of VBA programming as a whole. It’s important to understand the other parts like if statements, variables, and much more. If you want to learn the key concepts, check out my free 30-minute online course on Excel VBA here.

Other resources

While the VBA do until loop and do while loop are definitely cool, they’re not the only loop types around.

You’d be doing yourself a favor if you learned the others in my guide to VBA for loops here.

Or you can check out my big guide to VBA programming here.