Page 19 of 24PREVNEXT

Get in the loop with Excel macros

A Do...Loop in action

This Do…Loop macro combines two columns into a third.

With this macro, you could combine values from two columns into a third column, with a space between them. In the practice data, the columns to combine are First Name, Last Name, and Full Name, as shown in the illustration. You might be thinking "I could do this with a formula — is a macro really necessary?" And you would be right. You can do many things in Excel with formulas, and this process happens to be a prime candidate for a formula. However, this example also makes clear just how a Do…Loop works and what you can accomplish with this type of loop.

Sub LoopRange1()

In this example, the first name and last name are copied into the Full Name column with a space between them. This operation is performed while the value of Cells(x,3) is not blank. The variable x is used to track the current row number, and increasing x moves the operation to the next row. The columns are specified by the nonvariable values of 3, 4, and 5, which represent columns C, D, and E, respectively.

Tip    Pay careful attention to the comment text in green when you are trying to understand these VBA macros. When you are working in the Visual Basic Editor, you can insert more comments by typing an apostrophe at the beginning of a new line. VBA will ignore that line when it runs the macro.

Page 19 of 24PREVNEXT