You have list of items in Excel like different fruits. You would like to add a running number in front of the items.

You have:

Apple

Orange

Pear

Pineapple

But you would like to have:

1. Apple

2. Orange

3. Pear

4. Pineapple

The list should start with a running number.

The starting point is this.

You can do like this. Just copy and paste as values cells E3:E6. Then delete the cells B2:C6. The result is neat but requires some manual work.

We can have a VBA for this, too.

Sub z_runnum()

Dim zn

Dim za

zn = 1

Do Until ActiveCell = “”

    za = ActiveCell.Value

    ActiveCell.Value = zn & “. ” & za

    ActiveCell.Offset(1, 0).Select

    zn = zn + 1

Loop

End Sub

The VBA is even pretty short. Variable zn is the running number which starts from the one. If you would like to start with zero just set zn to zero, ZN = 0. Za is the value of the cell to be investigated. The VBA is looping till it hits an empty cell.

The value of the active cell is captured into variable za. The new content of the cell is running number zn, dot, space and the original value of the cell ZA.

Just place the cursor on the first cell value and execute VBA.

This is the result.

The benefits of VBA are that you don’t have to clean the data afterwards. It is fast and effective especially if you have large amounts of data.