Do Until Loop in VBA

A Do Until loop repeats a block of VBA code until a specified condition becomes True.

Basic structure:

Do Until condition

    statements

Loop

The loop continues while the condition is False. When the condition becomes True, the loop stops.

Do Until is useful when you know the condition that should eventually stop the loop.

• Processing worksheet data.
• Searching for a value.
• Processing rows until an empty cell is found.
• Repeating calculations.
• Generating sequential data.
• Processing student records.

The basic syntax is:

Do Until condition

    statements

Loop

VBA executes the statements and continues looping until the condition becomes True.

The following example displays numbers from 1 to 5.

Dim i As Integer

i = 1

Do Until i > 5

    MsgBox i

    i = i + 1

Loop

The loop stops when i > 5 becomes True.

The condition tells VBA when the loop should stop.

Do Until i > 5

As long as i > 5 is False, the loop continues. When it becomes True, the loop stops.

Before using a variable in the loop condition, give it an appropriate starting value.

Dim i As Integer

i = 1

Do Until i > 5

    MsgBox i

    i = i + 1

Loop

Here, i starts at 1.

The loop variable should normally be changed so that the stopping condition can eventually become True.

i = i + 1

If the value never changes appropriately, the loop may continue indefinitely.

You can use Do Until to write values into Excel cells.

Dim i As Integer

i = 1

Do Until i > 5

    Cells(i, 1).Value = i

    i = i + 1

Loop

This writes numbers 1 to 5 into cells A1 through A5.

A Do Until loop can repeatedly write text into worksheet cells.

Dim i As Integer

i = 1

Do Until i > 5

    Cells(i, 1).Value = "Student"

    i = i + 1

Loop

The word Student is written into five cells.

You can perform calculations during each iteration.

Dim i As Integer

i = 1

Do Until i > 10

    Cells(i, 1).Value = i * 10

    i = i + 1

Loop

The values 10, 20, 30 and so on are written into the worksheet.

An If statement can be placed inside a Do Until loop.

Dim i As Integer

i = 2

Do Until i > 10

    If Cells(i, 2).Value >= 40 Then
        Cells(i, 3).Value = "Pass"
    Else
        Cells(i, 3).Value = "Fail"
    End If

    i = i + 1

Loop

This example checks student marks from row 2 through row 10.

MsgBox can be used inside a Do Until loop.

Dim i As Integer

i = 1

Do Until i > 3

    MsgBox "Hello " & i

    i = i + 1

Loop

The message is displayed three times.

A counter is commonly used to control when a Do Until loop stops.

Dim count As Integer

count = 1

Do Until count > 10

    MsgBox count

    count = count + 1

Loop

The loop stops after the counter becomes greater than 10.

You can process a range using a row counter.

Dim rowNumber As Integer

rowNumber = 2

Do Until rowNumber > 10

    Cells(rowNumber, 1).Font.Bold = True

    rowNumber = rowNumber + 1

Loop

Rows 2 through 10 are processed.

Do Until can process student records row by row.

Dim rowNumber As Integer

rowNumber = 2

Do Until rowNumber > 11

    Cells(rowNumber, 4).Value = "Active"

    rowNumber = rowNumber + 1

Loop

The status of rows 2 through 11 is set to Active.

A common use of Do Until is to process data until an empty cell is found.

Dim rowNumber As Integer

rowNumber = 2

Do Until Cells(rowNumber, 1).Value = ""

    MsgBox Cells(rowNumber, 1).Value

    rowNumber = rowNumber + 1

Loop

The loop stops when an empty cell is found in column A.

A Do Until loop can be used to search through worksheet data.

Dim rowNumber As Integer

rowNumber = 2

Do Until Cells(rowNumber, 1).Value = "Rahul"

    rowNumber = rowNumber + 1

Loop

MsgBox "Search Completed"

The loop continues until the value in column A becomes Rahul.

When writing search code, it is important to also handle the possibility that the searched value does not exist.

A Do Until loop can calculate the total of worksheet values.

Dim rowNumber As Integer
Dim total As Double

rowNumber = 2
total = 0

Do Until rowNumber > 6

    total = total + Cells(rowNumber, 2).Value

    rowNumber = rowNumber + 1

Loop

MsgBox "Total = " & total

The values in cells B2 through B6 are added together.

A Do Until loop can contain multiple VBA statements.

Dim i As Integer

i = 1

Do Until i > 5

    Cells(i, 1).Value = i
    Cells(i, 2).Value = i * 10
    Cells(i, 3).Value = i * 100

    i = i + 1

Loop

Each iteration performs several operations.

You can create a multiplication table using Do Until.

Dim i As Integer
Dim number As Integer

number = 5
i = 1

Do Until i > 10

    Cells(i, 1).Value = number & " x " & i
    Cells(i, 2).Value = number * i

    i = i + 1

Loop

This creates the multiplication table of 5 from 1 to 10.

InputBox can be used inside a Do Until loop when there is a clear stopping condition.

Dim number As Integer
Dim count As Integer

count = 1

Do Until count > 3

    number = CInt(InputBox("Enter a number:"))

    MsgBox "You entered " & number

    count = count + 1

Loop

The user is asked to enter a number three times.

A Boolean variable can be used to control a Do Until loop.

Dim stopLoop As Boolean
Dim i As Integer

stopLoop = False
i = 1

Do Until stopLoop

    MsgBox i

    i = i + 1

    If i > 3 Then
        stopLoop = True
    End If

Loop

The loop stops when stopLoop becomes True.

Comparison operators can be used in the Do Until condition.

Dim i As Integer

i = 10

Do Until i <= 0

    MsgBox i

    i = i - 1

Loop

The loop continues until i <= 0 becomes True.

Do Until is useful when processing worksheet data until a specific condition is reached.

Dim rowNumber As Integer

rowNumber = 2

Do Until Cells(rowNumber, 1).Value = ""

    Cells(rowNumber, 2).Value = "Processed"

    rowNumber = rowNumber + 1

Loop

The loop processes records until an empty cell is found in column A.

The following example checks student marks until an empty student name is found.

Dim rowNumber As Integer

rowNumber = 2

Do Until Cells(rowNumber, 1).Value = ""

    If Cells(rowNumber, 2).Value >= 40 Then

        Cells(rowNumber, 3).Value = "Pass"

    Else

        Cells(rowNumber, 3).Value = "Fail"

    End If

    rowNumber = rowNumber + 1

Loop

Column A contains student names, column B contains marks, and column C receives the result.

Beginners commonly make these mistakes:

• Forgetting to initialize the loop variable.
• Forgetting to update the variable.
• Writing a condition that never becomes True.
• Using the wrong cell reference.
• Forgetting the Loop statement.
• Searching for data without handling missing values.

Always make sure the stopping condition can eventually become True.

The following example processes records until an empty cell is found.

Dim rowNumber As Integer

rowNumber = 2

Do Until Cells(rowNumber, 1).Value = ""

    Cells(rowNumber, 4).Value = "Processed"

    rowNumber = rowNumber + 1

Loop

MsgBox "Processing Completed"

This type of loop can be useful for simple Excel data-processing tasks.

• Clearly define the condition that should stop the loop.
• Initialize variables before starting the loop.
• Update the controlling variable when required.
• Make sure the stopping condition can become True.
• Test the loop with a small amount of data.
• Use meaningful variable names.
• Be careful when processing large worksheets.

A typical Do Until workflow is:

1. Declare the controlling variable.
2. Give the variable an initial value.
3. Write the Do Until condition.
4. Execute the required statements.
5. Update the controlling variable.
6. Reach the Loop statement.
7. Check the condition again.
8. Stop when the condition becomes True.

Dim i As Integer

i = 1

Do Until i > 5

    Cells(i, 1).Value = i

    i = i + 1

Loop

The Do Until loop is an important VBA looping structure. It repeats code until a specified condition becomes True.

It is especially useful when you want to process Excel data until a particular event or condition occurs.

Dim rowNumber As Integer

rowNumber = 2

Do Until Cells(rowNumber, 1).Value = ""

    If Cells(rowNumber, 2).Value >= 40 Then

        Cells(rowNumber, 3).Value = "Pass"

    Else

        Cells(rowNumber, 3).Value = "Fail"

    End If

    rowNumber = rowNumber + 1

Loop

In this example, VBA processes student records until an empty student name is found in column A. The marks are checked and the result is written into column C.

The next lesson will introduce Exit Statements, which allow you to leave a loop or procedure before it reaches its normal ending point.