Imagine you wanted to work to the bottom of a column of data in Excel until you reached the bottom. A “Loop” would help you do that. Loops are really handy. Macro loops allow you to repeat a certain action or actions until a particular condition is met (e.g. the data runs out).
- This is a key piece of code that’s going to help you loop to the bottom of a list. It’s a “Do until” loop. It works until it finds a cell that meets a certain condition. In this case it works until it finds a blank cell:
'Comment: starts loop
Do Until IsEmpty(ActiveCell)
'Comment: insert or separately record code that manipulates data and place it here
'Step down one row in column one
- There’s a fuller example of this loop at work under our definition for “Variables“.
- There are other types of loops apart from the do until loop. You only need one of them to work for you. With a bit of experimentation you will find that one might solve your problem in slightly fewer lines of code or take less time to run. Here are some examples of other macro loops solving the same problem.
- The “Do while” loop works while a condition is met. In this case it works while it can find a non-blank cell):
Do While ActiveCell.value <> ""
'Insert your code that manipulates data the way you want and place it here
'Step down one cell
- The “For each” loop works on each cell that meets a particular condition. In this case it works on each non-blank cell):
'Tells Excel which cells to look at
For Each MyCell In Range("A:A")
'Sets the condition. Repeats for all cells that meet the condition
If MyCell <> 0 Then
'Insert your code here