Using Excel VBA Macro to Add Numbering Bullets to Any Data

N.B.: For Code Scroll Down :)

Hello Everyone,
This is Sachin K here again,
Today while at work one of my colleague got into a trouble. He entered a lot of data into a spreadsheet like names of person and data related to their documents in front of their name.

Now all of a sudden he needs to insert a numbering bullets in various rows, similar to one can find in MS Word. Oh !!! Its not there !!!

I thought I could write a simple VBA code instead and make things easy for him.

Here is the situation (Screenshot 1):

You have data in row as seen below from Row No. 2 to Row No. 13.
You want to insert 1) , 2), 3), ..., etc. in front of each data in the same cells as they are.
It easy to put serial in empty cells in front of the data but remember that's not what we want here.

Screenshot 1 : The Problem

Here is the Solution (Screenshot 2):

The VBA Code runs and gives a result as shown in screenshot below

Screenshot 2 : The Solution

The VBA Code and Form used (Screenshot 3 & 4):

 The Solution for the problem is as follows

Screenshot 3 : The Visual Part

   The VBA form used to trigger numbering in excel sheet

Screenshot 4 : The Code

Here is a little explanation on how the code works:

When the user click on "Number IT !!!" Button on user form the Code Starts.

1. The Active Cell (Currently Selected Cell) Row number is stored in variable 'B'. That's starting row number.

2. Do While Loop Checks that Active Cell is empty or not. When the condition is true (Non Empty Active Cell) it follows next line of code, i.e. Stores the Row Number of Currently Selected Cell (Active Cell) to variable 'A'. Initially variables 'B' and 'A' are going to have same value.

3. The ActiveCell.Offset code activates cell on next row and 'loop' code loops this process till empty row is encountered, where Do While Condition is not met and code runs to next section. Remember, now variable 'A' is holding the value of empty row number !!!

4. Now we have row number of start i.e. 'B' and row number of first empty cell after the data 'A'

5. The variable 'C' is defined as difference between empty row and starting row i.e. (A-B). Why we need variable 'C'? We need that number so that we can reach up to the cell where we started. Also, it sets the interval for final numbering. 

Wait Why +1 in the formula? well that's because our Do While condition took us down till an empty cell was reached. So we reached one row below the data !

Hmm that's why formula C = (A-B) +1

5. So we do ActiveCell.Offset to (-C) and activate that cell. That's where our data started somewhere up (hence -C).

6. The we used a For ... Next loop to get the final numbering done.

For a range of values from 1 to 'C' the variable 'i' will run through each value and keep adding the data to cells via a simple code: ActiveCell.Value =  i & ")" & "space" & ActiveCell.Value

then go to next cell by offset method.

It Worked Well for me...!!

Do try for yourself and comment below. :)

The Code:

Private Sub CommandButton1_Click()
B = ActiveCell.Row
Do While ActiveCell.Value > Empty
A = ActiveCell.Row
ActiveCell.Offset(1, 0).Activate
Loop
C = (A - B) + 1
ActiveCell.Offset(-C, 0).Activate
For i = 1 To C
If ActiveCell.Value > Empty Then
ActiveCell.Value = i & ")" & " " & ActiveCell.Value
ActiveCell.Offset(1, 0).Activate
End If
Next i
End Sub