This is every useful VBA loop as it can be used for indefinite range with conditions applied. Like when you wanted to test starting to row 1 to last non-empty row or want to run macro for any particular condition only. You should remember that there is counter used for increment and this counter has to be part of criteria and there is Loop statement which rounds off the Do while statement. Loop is similar to Endif in **IF Then** or Next statement in **For next loop** as we have seen in my previous posts.

**Syntax:**

Do While (Criteria)

counter = counter + 1

'counter is has to be part of criteria

Loop

Do counter = counter + 1

'counter is has to be part of criteria

Loop While/Until (Criteria)

Now, lets look at few examples on Do loops. I am sure these examples will help you in learning if you practice them. Incase if you face any issue with replicating this code, please download the example file which has all example to try hands on. Also, you can make changes in code to test and enhance your skills.

**Example 1:** Sheet named 'Dowhile_DoUntil1' has data of all the cities with their scores. We want to scroll till non-empty row and want to display number of non-empty row. Remember we are only testing column 1.

-------------------------------------------------------------------------------------------------------------------------------

Sub Dowhile1()

Dim i As Long

'i is initialize to one as 1 is the minimum row on excel sheet and is very important step

i = 1

Do While Sheet1.Cells(i, 1) <> ""

i = i + 1

Loop

MsgBox "Last non-empty row is " & i - 1

End Sub

-------------------------------------------------------------------------------------------------------------------------------

Sub Dountil1()

Dim i As Long

i = 1

'i is initialize to one as 1 is the minimum row on excel sheet and is very 'important step

Do

i = i + 1

Loop Until Sheet1.Cells(i, 1) = ""

MsgBox "Last non-empty row is " & i - 1

End Sub

-------------------------------------------------------------------------------------------------------------------------------

If you observe carefully you will find the condition in Do while **(While Sheet1.Cells(i, 1) <> "" )**and Do Until **(Until Sheet1.Cells(i, 1) = "")** is exactly opposite.

**Example 2:** We want to move and test across rows and columns., Like matrix. For all cells containing more than 150 should be colored green and below 50 should be marked red. In practical use I would even prefer to use conditional formatting if I have to only use two formats. But actually you can use for this for having n number of conditional formats.

-------------------------------------------------------------------------------------------------------------------------------

Sub Dowhile2()

Dim i As Long

Dim j As Integer

i = 1

j = 1

Do While Sheet2.Cells(i, j) <> ""

Do While Sheet2.Cells(i, j).Value <> ""

If Sheet2.Cells(i, j) < 50 And IsNumeric(Sheet2.Cells(i, j)) = True Then

Sheet2.Cells(i, j).Interior.Color = vbRed

ElseIf Sheet2.Cells(i, j).Value > 150 And IsNumeric(Sheet2.Cells(i, j)) = True Then

Sheet2.Cells(i, j).Interior.Color = vbGreen

ElseIf (Sheet2.Cells(i, j).Value >= 50 And Sheet2.Cells(i, j).Value <= 150) And IsNumeric(Sheet2.Cells(i, j)) = True Then

Sheet2.Cells(i, j).Interior.Color = vbYellow

End If

i = i + 1

Loop

j = j + 1

i = 1

Loop

End Sub

Sub Dountil2()

Dim i As Long

Dim j As Integer

i = 1

j = 1

Do

Do

If Sheet2.Cells(i, j) < 50 And IsNumeric(Sheet2.Cells(i, j)) = True Then

Sheet2.Cells(i, j).Interior.Color = vbRed

ElseIf Sheet2.Cells(i, j).Value > 150 And IsNumeric(Sheet2.Cells(i, j)) = True Then

Sheet2.Cells(i, j).Interior.Color = vbGreen

ElseIf (Sheet2.Cells(i, j).Value >= 50 And Sheet2.Cells(i, j).Value <= 150) And IsNumeric(Sheet2.Cells(i, j)) = True Then

Sheet2.Cells(i, j).Interior.Color = vbYellow

End If

i = i + 1

Loop Until Sheet2.Cells(i, j).Value = ""

j = j + 1

i = 1

Loop Until Sheet2.Cells(i, j) = ""

End Sub

IsNumeric(Sheet2.Cells(i, j)) = True test whether the value is numeric or not. IsNumeric() is very useful while scanning range of cells and you want to check or apply code only to cell with numeric values.

I will post some tips where you will see an extensive use of Do while/Until loop in MS Excel/VBA as Do while/until is most useful loop. Also, for your convenience I have posted link to download MS Excel file.

Download Do while/Until Loop

Kindly let us know your comments about this post.

**We assure knowledge. Not SPAM.**

## 4 comments:

Dear Sarfaraz Ahmed

I read your blog from many times , it is too informative and education thanks but when I try to add you technorati as favorite , I unable to do that see in your technorati banner what is the problem

May 6, 2009 at 8:42 PMI tried adding it to technorati it works fine.. Please let me know have you tried using share button on botton of each post. Also, please let me know if you see any error.

May 6, 2009 at 8:48 PMyes now I have add you in my technorati favorites . I will enjoy to read next time

Dear I also want to know how to show in email subscriber a very short para and say

Visit blog for more details

I also want to activate this system in my blog also .

Thanks

you can tell me at vinod_13242002@yahoo.com

May 6, 2009 at 8:51 PMi am looking for a way to have a loop till a condition is met. for example:

=if(cellZ>0 then(celly+cellx)which y+x stores a cellA, then cella increased by cellx, once cella is meets a condtion then the loop will stop, it would be prefered to have the loop initialize only if cellz>0 otherwise the loop is inactive, i searched for good examples that use cells in the reference but i dont know how to code it

July 17, 2009 at 1:06 AM## Post a Comment