Looping in VBA can be one of the most powerful things you can do as it allows you to process an entire range of information with only a few lines of code.
The standard setup for a loop is the below.
Do 'insert code to loop through here Loop
VBA processes code from top to bottom. When your code is processing, it will hit the Do line first and continue on as normal. Once it hits the loop however, it will go back up to the Do and process that code all over again. The code will continue doing this until told otherwise.
A warning in advance, if you have not told your code to exit the loop at some point, it will continue looping until you manually stop/break the code processing. This can typically be done by pressing the esc key or ctrl+shift+end.
Lets use the below for example to show a basic loop. With this data, I want to know if the value in column A is greater than 10. If the value is greater than 10, tell me ‘Yes’ in column B or tell me ‘No’ if it is not. Use F8 to step through your code line by line to see how it is processing.
Sub loop_training() Range("A2").Select Do If ActiveCell.Value > 10 Then ActiveCell.Offset(0, 1).Value = "Yes" Else ActiveCell.Offset(0, 1).Value = "No" End If Loop End Sub
My code starts off by selecting column A. The code then enters my loop where I am using an If-Then-Else statement to evaluate the value of my current cell, A2. Five is not greater than ten so it logs No in cell B2. Keep pressing F8 and you will notice something. Nothing else is happening. We keep going to the top of our loop, reevaluating our If statement for the same cell and then doing it all over again.
Telling the loop to move down our column
All we need to do to our loop to cause it to run down our list of cells is to offset by one row for each loop iteration. This can be done by adding
The below GIF shows how this has effected our loop. We now run down the list values; however, we are still stuck in a continuous loop. We need to tell our loop when to stop looping.
Telling the loop when to stop looping
We can tell our loop to stop in numerous ways; I am going to highlight two that I use pretty frequently when looping using an Active Cell. The first is to stop the loop when my active cell is empty (null) and the second is to stop the loop when I have reached the end of my data.
The first option can be accomplished by changing our Do statement to
Do Until IsEmpty(ActiveCell)
This will have the below effect on our code.
Notice how our code did not finish running through all of our cells. This is because we have a blank value in our column. Typically, this option will be fine as most sets of data do not have a blank cell until the end of the data set; however, in this case, looping until we encounter our first blank cell is not the option we want.
The second option can be accomplished by adding a variable to our code before the loop. Variable lastrow will tell us what the last row in our data is.
lastrow = Range("A" & Rows.Count).End(xlUp).Row
You can always test your variables by stepping through the code with F8 and hovering your mouse over the variable.
We also want to remove our
Do Until IsEmpty(ActiveCell)and change it to
Do Until ActiveCell.Row > lastrow. Since our last row is 10, we want the code to stop once our Activecell is on row 11.
You can now run the macro without stepping through with F8 in order to process everything in milliseconds.
If you liked this content and found it helpful, please like the page and leave a comment below. You can also ask questions on the Forum to get personalized help on your issues and questions.