How to Use Do Until and Do While Loops in VBA

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.

And if you can master them in VBA, you’ll be prepared to create powerful scripts to work with spreadsheet data in new ways.

Let’s take a look at do until loops first, then discuss the important difference between the two types.

Get your FREE exercise file

We’ll be using an example workbook in the coming sections.

Download the workbook for free by clicking the button below!

Download the FREE Exercise File

Download exercise file

Using do until loops in VBA

Before we get into the specific syntax of the 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.

A do until loop works on the same principle.

Kasper Langmann, Co-founder of Spreadsheeto

The example spreadsheet linked above has a series of first names in column A and last names in column B. Let’s write a script to combine them into full names in column C.

Here’s the VBA script we’ll use:

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 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.

This is important; a do while loop is the exact opposite, and it’s easy to mix them up.

Kasper Langmann, Co-founder of Spreadsheeto

The next line is the command that 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:

vba-do-until-results

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 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, 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 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 this script:

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, this says that as long as (i, 1) or (i, 2) contains a value, the rest of the loop will run. 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:

do-while-results

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.

Power up Excel with do loops

Similar to other programming languages, VBA features loops as an important component.

If you understand loops, you can do just about anything. It can take a while to get the hang of do loops, but with some practice, they’ll become much easier.

Just remember to think carefully about whether you’re using do until or do while, and you’ll be able to iterate through your spreadsheets to accomplish any task you need.