Goal: Demonstrate to Excel users how to write a basic looping macro through rows in a column.

This is a follow up to an earlier video on writing a simple loop in Excel VBA. However in this article, we will compose a basic loop macro of rows in a column in Excel using Visual Basic for Applications.

Most Excel users are aware we can drag down a couple of cells like this, in order to generate a pattern of cell values down rows in a column. For example from 1 to 10 like this.

But instead we will draft a simple macro to perform the same output. The primary aim from this video is to demonstrate how Excel users can write a simple looping macro based on a single parameter. More specifically, we will write a looping macro to loop through rows in a column in a spreadsheet.

Writing the Loop macro

Go to Visual Basic for Applications, and insert a Module. We will call this macro, loopRowsColumn

Generally speaking, it is advisable to add the Option Explicit syntax at the top of the module. The Option Explicit will ensure we declare all of the variables in a specific macro such as this one. It will reduce the risk of run-time errors whilst the macro is running

Next we should disable these Excel properties, which denote the calculation, event and screen properties of Excel. Our macro will function more efficiently by disabling these properties, whilst we execute on our looping code. Then we want to declare our object variables for this macro.

The i object will be declared as a long variable, which can store long numeric values from 0 to 2,147,483,647 and 0 to -2,147,483,648 values. Whilst both Rng and startCell will be declared as range object variables, because these objects need to denote cells or multiple cells in VBA.

The startCell range object will be declared as the starting or active cell for this macro. On the other hand, rng will be the selected range of cells.

The Loop Rows Column

Next is the major learning aspect of this video; the basic looping code to cycle down rows in a column based on a simple criteria. The For … Next loop is one technique to loop through a range of cells.

In this example, the loop will execute based on the i object variables of 1 to 10

Given we need the first cell or startCell to be the first cell to input a value, we want the OFFSET syntax to be 0,0 for the first loop of i – when i = 1. Given we don’t want to shift the cursor down or up any rows nor columns for the first loop.

Thus the first loop of i, 1, will return the output value of 1. The i variable can now move to 2, in order for this loop to run again – the cursor will move one row down and input the value of 2. Our loop will continue to loop until the i variable reaches 10.

It will then exit the loop, and return the cursor back to the initial cell or startCell object variable. The looping is complete, we can now re-enable those three Excel properties that were disabled at the start of our macro.

Our macro has finished running. Before we test the macro in our spreadsheet, why not review the macro one last time.

Further materials

Let’s run the macro.

As we can see, the macro is correctly looping down the rows in a column of cells, and returning the individual number outputs. Here is the example workbook, which contains the loop rows column macro.

Download Spreadsheet Vault SpreadsheetVault – Loop through rows in a Column (solution).xlsb