Do While Loop in Excel VBA – Complete Guide
Welcome to the complete Do While Loop in Excel VBA tutorial.
The Do While Loop is one of the most important looping structures in VBA. It is used when you want to repeatedly execute a block of code while a particular condition remains True.
Unlike a traditional For Loop, you do not always need to know in advance how many times the code should execute.
WHILE condition is TRUE
Execute code
REPEAT
1. What You Will Learn
In this lesson we will cover the Do Loop family from beginner level to practical Excel VBA automation.
- What is a Do While Loop?
- Why Do While Loop is used
- Do While vs For Loop
- Basic Do While syntax
- How the condition works
- Do While...Loop
- Do...Loop While
- Do Until...Loop
- Do...Loop Until
- While vs Until
- Condition at beginning vs end
- Counter based Do While
- Do While with Excel cells
- Do While with rows
- Do While with columns
- Do While with If
- Do While with user input
- Exit Do
- Nested Do While loops
- Infinite loops
- Practical Excel projects
- Common mistakes
- Best practices
- Practice exercises
- Practical quiz
2. What is a Do While Loop?
A Do While Loop repeatedly executes VBA statements as long as a specified condition is True.
Consider this example:
Sub BasicDoWhile()
Dim i As Long
i = 1
Do While i <= 5
Debug.Print i
i = i + 1
Loop
End Sub
The output in the Immediate Window will be:
1 2 3 4 5
The loop continues while:
i <= 5
is True.
When i becomes 6, the condition becomes False and
the loop stops.
3. Why Do While Loop?
A Do While Loop is especially useful when the number of iterations depends on a condition.
Some common examples are:
- Read Excel rows until a blank row is found.
- Continue processing until a particular value is found.
- Ask the user for valid input.
- Continue calculations until a target is reached.
- Process records until the end of available data.
- Repeat an operation while a condition remains True.
Use a For Loop when the number or range of iterations is known.
Use a Do While Loop when the continuation of the loop depends mainly on a condition.
4. For Loop vs Do While Loop
| For Loop | Do While Loop |
|---|---|
| Usually used when the number of iterations is known. | Usually used when the stopping condition determines the number of iterations. |
For i = 1 To 100 Next i |
Do While condition Loop |
| Counter is built into the loop structure. | You generally control the condition yourself. |
5. Basic Do While Syntax
The basic syntax is:
Do While condition
'Code to execute
Loop
Example:
Sub Example()
Dim i As Long
i = 1
Do While i <= 10
Debug.Print i
i = i + 1
Loop
End Sub
Understanding Each Part
| Code | Purpose |
|---|---|
Dim i As Long
|
Creates a numeric variable. |
i = 1
|
Initializes the variable. |
Do While i <= 10
|
Checks whether the condition is True. |
Debug.Print i
|
Executes the code inside the loop. |
i = i + 1
|
Changes the value so the loop can eventually stop. |
Loop
|
Returns to the condition. |
6. How the Do While Condition Works
Consider:
Dim i As Long
i = 1
Do While i <= 5
Debug.Print i
i = i + 1
Loop
VBA evaluates the condition before every iteration.
| Iteration | i | Condition | Action |
|---|---|---|---|
| 1 | 1 | True | Execute |
| 2 | 2 | True | Execute |
| 3 | 3 | True | Execute |
| 4 | 4 | True | Execute |
| 5 | 5 | True | Execute |
| 6 | 6 | False | Stop |
7. Important: The Loop May Execute Zero Times
Because the condition is checked before the loop body, the code may execute zero times.
Sub ZeroIteration()
Dim i As Long
i = 20
Do While i <= 10
Debug.Print i
i = i + 1
Loop
End Sub
Here:
20 <= 10
is False from the beginning.
Therefore the loop does not execute.
8. Do...Loop While
VBA also allows the condition to be placed at the end.
Syntax
Do
'Code
Loop While condition
Example
Sub LoopWhileExample()
Dim i As Long
i = 1
Do
Debug.Print i
i = i + 1
Loop While i <= 5
End Sub
The condition is checked at the end.
Therefore the code executes at least once.
9. Do Until...Loop
Do Until is another member of the Do Loop family.
It means:
Syntax
Do Until condition
'Code
Loop
Example
Sub UntilExample()
Dim i As Long
i = 1
Do Until i > 5
Debug.Print i
i = i + 1
Loop
End Sub
10. Do...Loop Until
Here the condition is checked at the end.
Do
'Code
Loop Until condition
Example:
Sub LoopUntilExample()
Dim i As Long
i = 1
Do
Debug.Print i
i = i + 1
Loop Until i > 5
End Sub
11. The Four Do Loop Forms
| Syntax | Condition | Can Execute Zero Times? |
|---|---|---|
| Do While...Loop | At beginning | Yes |
| Do...Loop While | At end | No |
| Do Until...Loop | At beginning | Yes |
| Do...Loop Until | At end | No |
12. While vs Until
| While | Until |
|---|---|
| Continue while condition is True. | Continue until condition becomes True. |
Do While i <= 10 |
Do Until i > 10 |
These two examples produce the same result:
Do While i <= 10
Debug.Print i
i = i + 1
Loop
and:
Do Until i > 10
Debug.Print i
i = i + 1
Loop
13. Do While with Excel Cells
Now we move from basic programming examples to practical Excel automation.
Suppose column A contains:
A1 = Customer ID A2 = 1001 A3 = 1002 A4 = 1003 A5 = 1004 A6 = blank
We can process the data until a blank cell is found.
Sub ReadUntilBlank()
Dim i As Long
i = 2
Do While Cells(i, 1).Value <> ""
Debug.Print Cells(i, 1).Value
i = i + 1
Loop
End Sub
14. Understanding Cells(i, 1)
The expression:
Cells(i, 1)
means:
Row = i Column = 1
Column 1 is column A.
Therefore:
| Code | Cell |
|---|---|
| Cells(2,1) | A2 |
| Cells(3,1) | A3 |
| Cells(4,1) | A4 |
| Cells(5,1) | A5 |
15. Write Serial Numbers Using Do While
Suppose column B contains employee names. We want to create serial numbers in column A.
Sub CreateSerialNumbers()
Dim i As Long
i = 2
Do While Cells(i, 2).Value <> ""
Cells(i, 1).Value = i - 1
i = i + 1
Loop
End Sub
The loop continues while column B contains data.
16. Do While with If Statement
A very common real-world combination is:
Sub CheckSales()
Dim i As Long
i = 2
Do While Cells(i, 1).Value <> ""
If Cells(i, 2).Value >= 50000 Then
Cells(i, 3).Value = "High"
Else
Cells(i, 3).Value = "Normal"
End If
i = i + 1
Loop
End Sub
17. Find the First Matching Value
Do While is useful when you want to search through records until a particular value is found.
Sub FindCustomer()
Dim i As Long
Dim searchName As String
searchName = InputBox("Enter customer name")
i = 2
Do While Cells(i, 1).Value <> ""
If Cells(i, 1).Value = searchName Then
MsgBox "Customer found in row " & i
Exit Do
End If
i = i + 1
Loop
End Sub
18. Exit Do
Exit Do immediately terminates the current Do loop.
Sub ExitDoExample()
Dim i As Long
i = 1
Do While i <= 100
If i = 10 Then
Exit Do
End If
Debug.Print i
i = i + 1
Loop
End Sub
The loop stops when i reaches 10.
19. Do While with User Input
One practical use is validating user input.
Sub GetValidNumber()
Dim number As Long
number = 0
Do While number <= 0
number = Val(InputBox( _
"Enter a number greater than 0"))
Loop
MsgBox "Valid number entered: " & number
End Sub
If the user enters 0 or a negative number, the loop asks again.
20. Do While User Menu
Do While can also be used to create a simple menu-driven program.
Sub SimpleMenu()
Dim choice As String
choice = ""
Do While choice <> "Q"
choice = UCase(InputBox( _
"Enter A for Add" & vbCrLf & _
"Enter B for Browse" & vbCrLf & _
"Enter Q to Quit"))
If choice = "A" Then
MsgBox "Add selected."
ElseIf choice = "B" Then
MsgBox "Browse selected."
ElseIf choice <> "Q" Then
MsgBox "Invalid option."
End If
Loop
MsgBox "Program ended."
End Sub
21. Calculate Total Until Blank
Suppose column A contains sales amounts.
Sub CalculateTotal()
Dim i As Long
Dim total As Double
i = 2
total = 0
Do While Cells(i, 1).Value <> ""
total = total + Cells(i, 1).Value
i = i + 1
Loop
MsgBox "Total Sales = " & total
End Sub
22. Continue Until Target is Reached
Here the number of iterations is unknown.
The loop stops when the total reaches ₹1,00,000.
Sub ProcessUntilTarget()
Dim i As Long
Dim total As Double
i = 2
total = 0
Do While total < 100000
total = total + Cells(i, 1).Value
i = i + 1
Loop
MsgBox "Target reached."
End Sub
In a real project, you should also check whether there are enough rows before continuing. Otherwise the loop could move beyond the available data.
23. Nested Do While Loops
A Do While loop can be placed inside another Do While loop.
Sub NestedDoWhile()
Dim i As Long
Dim j As Long
i = 1
Do While i <= 3
j = 1
Do While j <= 3
Debug.Print "i = " & i & _
", j = " & j
j = j + 1
Loop
i = i + 1
Loop
End Sub
The inner loop completes before the outer loop moves to the next iteration.
24. Infinite Loop
An infinite loop occurs when the condition never becomes False.
Incorrect Example
Sub InfiniteLoop()
Dim i As Long
i = 1
Do While i <= 10
Debug.Print i
Loop
End Sub
Why is this an infinite loop?
Because i is never increased.
Therefore:
i <= 10
remains True forever.
Correct Example
Sub CorrectLoop()
Dim i As Long
i = 1
Do While i <= 10
Debug.Print i
i = i + 1
Loop
End Sub
25. Do While with Multiple Conditions
You can combine conditions using logical operators.
Using And
Do While i <= 100 And Cells(i, 1).Value <> ""
Debug.Print Cells(i, 1).Value
i = i + 1
Loop
Both conditions must be True.
Using Or
Do While i <= 100 Or Cells(i, 1).Value <> ""
'Code
i = i + 1
Loop
An OR condition remains True if either condition is True. This can sometimes create an unexpected or very long loop.
26. Practical Project – Process Customer Data
Assume:
| Column | Data |
|---|---|
| A | Customer Name |
| B | Sales |
| C | Status |
VBA:
Sub ProcessCustomerData()
Dim i As Long
i = 2
Do While Cells(i, 1).Value <> ""
If Cells(i, 2).Value >= 100000 Then
Cells(i, 3).Value = "VIP"
ElseIf Cells(i, 2).Value >= 50000 Then
Cells(i, 3).Value = "Good"
Else
Cells(i, 3).Value = "Normal"
End If
i = i + 1
Loop
MsgBox "Customer processing completed."
End Sub
27. Practical Project – Find First Error
Sub FindFirstError()
Dim i As Long
i = 2
Do While Cells(i, 1).Value <> ""
If UCase(Trim(Cells(i, 2).Value)) = "ERROR" Then
MsgBox "Error found in row " & i
Exit Do
End If
i = i + 1
Loop
End Sub
28. Practical Project – Generate Serial Numbers
Sub GenerateSerialNumbers()
Dim i As Long
i = 1
Do While i <= 100
Cells(i, 1).Value = i
i = i + 1
Loop
End Sub
29. Practical Project – Read Until "STOP"
Suppose column A contains records and the word STOP indicates that processing should end.
Sub ReadUntilStop()
Dim i As Long
i = 2
Do While UCase(Cells(i, 1).Value) <> "STOP"
Debug.Print Cells(i, 1).Value
i = i + 1
Loop
End Sub
30. Practical Project – Copy Data Until Blank
Sub CopyDataUntilBlank()
Dim i As Long
i = 2
Do While Cells(i, 1).Value <> ""
Cells(i, 5).Value = Cells(i, 1).Value
i = i + 1
Loop
MsgBox "Data copied."
End Sub
31. Common Mistakes
Mistake 1 – Forgetting to Update the Counter
i = 1
Do While i <= 10
Debug.Print i
Loop
This can create an infinite loop.
Mistake 2 – Wrong Condition
Always test your condition carefully.
Do While i > 10
If i = 1, this loop will never execute.
Mistake 3 – Confusing While and Until
Do While i <= 10
means:
Continue while the condition is True.Do Until i > 10
means:
Continue until the condition becomes True.Mistake 4 – Forgetting Exit Do
If a search should stop as soon as the target is found,
consider using Exit Do.
32. Best Practices
- Always initialize your variables.
- Make the loop condition easy to understand.
- Ensure the condition can eventually become False.
-
Use
Exit Dofor controlled early exits. -
Use
Longrather thanIntegerfor Excel row counters. - Qualify Excel objects when writing professional code.
- Avoid unnecessarily large loops.
- For very large datasets, consider processing data using arrays.
33. Better Professional Code
Instead of:
Do While Cells(i, 1).Value <> ""
Debug.Print Cells(i, 1).Value
i = i + 1
Loop
A more professional approach is to specify the worksheet.
Sub ProfessionalExample()
Dim ws As Worksheet
Dim i As Long
Set ws = ThisWorkbook.Worksheets("Data")
i = 2
Do While ws.Cells(i, 1).Value <> ""
Debug.Print ws.Cells(i, 1).Value
i = i + 1
Loop
End Sub
The code clearly tells VBA which worksheet should be used. This reduces problems caused by the user changing the active worksheet.
34. Do While Cheat Sheet
| Syntax | Meaning |
|---|---|
Do While condition
|
Continue while condition is True. |
Loop While condition
|
Continue while condition is True after executing the body. |
Do Until condition
|
Continue until condition becomes True. |
Loop Until condition
|
Continue until condition becomes True after executing the body. |
Exit Do
|
Immediately exits the Do loop. |
35. Practice Exercises
Beginner Exercises
Exercise 1: Print numbers from 1 to 10 using Do While.
Exercise 2: Print numbers from 10 down to 1 using Do While.
Exercise 3: Write numbers 1 to 100 into column A.
Exercise 4: Calculate the sum of numbers from 1 to 100.
Intermediate Exercises
Exercise 5: Read column A until a blank cell is found.
Exercise 6: Find the first occurrence of "Pending".
Exercise 7: Copy values from column A to column B until a blank row.
Exercise 8: Ask the user for a number greater than 100 and keep asking until a valid value is entered.
Advanced Exercises
Exercise 9: Read sales values from column A until the total reaches ₹1,00,000.
Exercise 10: Search for a customer name and stop immediately when the customer is found.
Exercise 11: Process all rows until the word "STOP" is found.
Exercise 12: Create a menu-driven program using Do While.
36. Practical Quiz
Question 1: What does a Do While loop do?
A. Runs while its condition is True
B. Runs exactly 10 times
C. Runs only once
D. Runs only when the condition is False
A Do While loop continues executing while its condition evaluates to True.
Question 2: What is the output?
Dim i As Long
i = 1
Do While i <= 3
Debug.Print i
i = i + 1
Loop
A. 1, 2, 3
B. 0, 1, 2
C. 1, 2
D. Infinite loop
The values printed are 1, 2 and 3.
Question 3: Where is the condition checked in Do While...Loop?
A. Beginning
B. Middle
C. End
D. After the Sub procedure
The condition is checked before the loop body executes.
Question 4: Which loop checks the condition at the end?
A. Do While...Loop
B. Do...Loop While
C. For...Next
D. Select Case
Do...Loop While evaluates its condition after executing the loop body.
Question 5: Which statement immediately exits a Do loop?
A. Exit Loop
B. Break Do
C. Exit Do
D. Stop Loop
Exit Do immediately terminates the current Do loop.
Question 6: What is wrong with this code?
i = 1
Do While i <= 10
Debug.Print i
Loop
A. Nothing
B. It creates an infinite loop
C. It automatically stops
D. It is a For loop
The value of i never changes, so the condition remains
True.
Question 7: Which statement means "continue until the condition becomes True"?
A. Do While condition
B. Do Until condition
C. For condition
D. Loop While condition
Do Until continues until the specified condition becomes True.
Question 8: Which loop is useful for reading Excel rows until a blank cell?
A. Do While
B. Select Case
C. With
D. Property
Do While can continue processing while the current cell contains data.
Question 9: Which situation is better suited to a Do While loop?
A. Repeat exactly 10 times
B. Continue processing until a condition changes
C. Print exactly 1 to 10
D. Process exactly 5 rows
Do While is especially useful for condition-controlled repetition.
Question 10: What is the major difference between Do While...Loop and Do...Loop While?
A. One works only with Excel
B. One checks the condition before execution and the other after execution
C. One works only with numbers
D. There is no difference
Do While...Loop checks the condition before executing the body, while Do...Loop While checks it after executing the body.
37. Final Do Loop Summary
| Loop | When Condition is Checked | Main Idea |
|---|---|---|
| Do While...Loop | Beginning | Continue while condition is True. |
| Do...Loop While | End | Execute first, then continue while True. |
| Do Until...Loop | Beginning | Continue until condition becomes True. |
| Do...Loop Until | End | Execute first, then continue until True. |
38. Most Important Concepts to Remember
Do While
Do While condition
'Code
Loop
Think: "Keep running while this condition is True."
Do Until
Do Until condition
'Code
Loop
Think: "Keep running until this condition becomes True."
Condition at Beginning
Do While condition
'Code
Loop
The loop may execute zero times.
Condition at End
Do
'Code
Loop While condition
The loop executes at least once.
Exit Do
Exit Do
Immediately terminates the current Do loop.
39. Quick Interview Questions
- What is the difference between For and Do While?
- What is the difference between Do While and Do Until?
- Where is the condition checked in Do While...Loop?
- Where is the condition checked in Do...Loop While?
- Can a Do While loop execute zero times?
- Can a Do...Loop While execute at least once?
- What is an infinite loop?
- How do you terminate a Do loop immediately?
- How can Do While be used to process Excel rows?
- When should you use an array instead of repeatedly reading worksheet cells?
40. Next VBA Lesson
After completing this lesson, the recommended next topic is:
Do Until Loop – Complete Guide
- Do Until...Loop
- Do...Loop Until
- While vs Until
- Do Until with Excel Rows
- Do Until with User Input
- Exit Do
- Nested Do Until
- Infinite Loops
- Real-world Excel Automation
- Practice Exercises
- Advanced Quiz
Do not just read the examples. Open Excel, press ALT + F11, create a Module, type the examples yourself, and run them.
The best way to learn VBA is:
Learn → Type → Run → Make Mistakes → Debug → Practice
Smarter Work Beats Hard Work.
0 Comments