Goal: Show Excel users how to compose a simple looping macro across columns in a row.

This tutorial will walk you through a simple loop of columns in a row. Here is a sneak peak of the output, which we will generate from this basic loop macro in Excel using Visual Basic for Applications.

Most Excel users know we can drag across a couple of cells like this, in order to generate a pattern of cell values. For example from 1 to 10 like this. We can draft a simple macro to perform the same output.

The main goal from this video is to show Excel users how to compose a simple looping macro based on a simple parameter. More specifically, we will write a looping macro to loop through columns in a row in a spreadsheet.

The Loop macro

Go to Visual Basic for Applications, and insert a Module. In general, it is wise to add the Option Explicit syntax at the top of the module. The Option Explicit will it force us to declare all of the variables in a specific macro such as this one.

It will help to lessen the risk of run-time errors whilst the macro is running. Let’s call the macro, loopColumnsRow

Remember to disable these Excel properties, which pertain to the calculation, event and screen properties. Then we need to declare our object variables

The i object will be declared as a long variable. The great thing with a long variable is it can store long numeric values. Any values from 0 to 2, 147, 483, 647 and 0 to -2, 147, 483, 648 are permissible.

Whilst both Rng and startCell will be declared as range object variables, as we need these objects to denote cells or multiple cells in VBA. These startCell range object will be declared as the starting or active cell for this exercise, whilst rng will be the selected range of cells.

The Loop Column Row syntax

We are now ready to add the key learning component of this video; the basic looping code to cycle across columns in a row based on a simple criteria.

The For … Next loop is one approach to loop through a range of cells. In this example, the loop will run based on the i object variables of 1 to 10.

Given we want 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 move the cursor down or up any rows nor columns for the first loop.

Hence the first loop of i, 1, will generate 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 column across 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 place the cursor back at the initial cell or startCell object variable.

The macro has finished running, we can now re-enable those three Excel properties that were previously disabled. Our macro is now complete. Let’s review our macro, before we run the macro in our spreadsheet.

Running the macro

Let’s run the macro. As we can see, the macro is successfully looping across the columns in a row of cells, and generating the respective number outputs.

Feel free to like this post, send us a comment or subscribe to our channel.

Download Spreadsheet Vault SpreadsheetVault – Loop across columns in a Row (solution).xlsb