O'Reilly logo

Excel® VBA: 24-Hour Trainer by Tom Urtis

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Chapter 9. Repeating 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 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 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 VBA, a loop is a structure ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required