Excel VBA 24-Hour Trainer, 2nd Edition

Lesson 10Repeating Actions with Loops

Suppose you need to perform the same action, or the same sequence of several actions, many times in your macro. For example, you may need to unhide all worksheets that are hidden, or you need to add 12 worksheets to your workbook and name them for each month of the year.

The fact is, you'll encounter many circumstances for which a repetition of similar commands is a necessary part of the job. In most cases it will be impractical, and sometimes downright impossible, to write an individual command for each performance of the action. The need for handling a repetitive set of commands efficiently is exactly what loops are made for.

What is a Loop?

A loop is a method of performing a task more than once. You may need to copy each worksheet in your workbook and save it as the only worksheet in its own separate workbook. Or, you may have a list of thousands of records and you want to insert an empty row where the value of a cell in column A is different than the value of the cell below it. Maybe your worksheet has dozens of cells that contain comments, and you want to add the same preceding text to every comment's existing text without having to edit every comment one at a time.

Instead of doing these kinds of tasks manually, or recording an impractical (and sometimes impossible) macro to handle the repetition, you can use loops to get the job done with less code while keeping more flexible control over the number of necessary repetitions. In ...