Auto Generate Serial Number in Excel VBA (4 Ways)

The sample dataset Customer Credentials, into which serial numbers are to be inserted adjacent to those entries.

Method 1 – Using VBA for Auto Generating Serial Number Depending On Adjacent Column

• Add or insert a Serial Number column adjacent to the range as shown in the following image.

• Press CTRL+11 to open Microsoft Visual Basic.
• Click on Insert > Module.
• Paste the following macro in the Module.

Sub AutoGeneratedSerialNumber()
For m = 4 To Cells(Rows.Count, "C").End(xlUp).Row
If Cells(m, "C").Value <> "" Then
Cells(m, "B").Value = m - 3
End If
Next m
End Sub

• Start the macro procedure by declaring the Sub name.
•  The VBA FOR statement creates a loop to execute a condition using the VBA IF function. The IF function checks for non-blank cells in the adjacent column (i.e., Column C). Afterward, the function inserts the serial numbers depending on the non-blank cells in the adjacent column.
• Press F5 to run the macro and return to the active worksheet.

Method 2 – Generating Dynamic Serial Number Using VBA in Excel

In this example there is no content in the table yet.

• Open Microsoft Visual Basic then double-click on any Sheet to display the Worksheet’s Code window.
• Enter the below macro in the window.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim m As Long
m = Application.WorksheetFunction.CountA(Sheet3.Range("C:C"))
If m > 1 Then
Sheet3.Range("B" & m + 2).Value = m - 1
End If
End Sub

The marked items define the following steps.

1. Start the private macro with a selection change.
2. Define and assign the variable to the COUNTA function to count existing entries in Column C.
3. Execute the VBA IF function to insert values as dynamic serial numbers.

• After inserting the macro, return to the worksheet and add Customer Names. Excel automatically inserts the serial number with formula as you hit ENTER as in the following image.

Method 3 – Using Macro Button to Auto Generate Serial Number with Fixed Length

Insert and assign macro to a Macro Button. Paste the below macro in the Command Button’s macro space.

Private Sub AutoGenerateSN_Click()
On Error Resume Next
On Error GoTo 0
Dim UsedRow As Long
With ActiveSheet
UsedRow = .Cells(.Rows.Count, "B").End(xlUp).Row
TextChar = Left(.Cells(UsedRow, "B"), 2)
NumChar = Right(.Cells(UsedRow, "B"), 5) + 1
If Len(NumChar) < 5 Then
For m = 1 To (5 - Len(NumChar))
NumChar = "0" & NumChar
Next m
End If
.Cells(UsedRow + 1, "B").Value = TextChar & NumChar
End With
End Sub

This results in the following steps.

1. Declares the macro and assigns the variable.
2. Inserts the serial number using a VBA WITH statement.
3. Assigns values to the variable. The TextChar fetches the left 2 text characters using the LEFT function, and the RIGHT function brings up the number characters.
4. Executes a condition using the VBA IF function. The condition is to add zeros if serial numbers don’t add up to a total 5 number characters. The LEN function counts the number of characters.

• In the worksheet, click on the Macro Button to insert serial numbers one at a time. Each click runs the macro and inserts a serial number as shown below.

Method 4 – Inserting Serial Number in Excel VBA Up to a User Defined Number Automatically

• Place the cursor in the cell from where you want the serial numbers (i.e., B4).

• Insert a Module in Microsoft Visual Basic Window then enter the following macro within the module.

Sub SerialNumberUptoX()
Dim m As Integer
On Error GoTo Last
m = InputBox("X", "Provide the Highest Serial Number")
For m = 1 To m
ActiveCell.Value = m
ActiveCell.Offset(1, 0).Activate
Next m
Last: Exit Sub
End Sub

This results in the following steps.

1. Start the macro by setting the Sub name.
2. Declare the variable and assign it to bring up an Inputbox. In the inputbox, enter the largest serial number you want to insert for the entries of adjacent cells.
3. Create a loop using VBA FOR to place the serial number from the active cell to the largest number.

• Hit F5 to run the macro, Excel fetches the Provide the Highest Serial Number window as depicted in the following image.
• Enter the highest serial number (i.e.,10).
• Click OK.

• Excel inserts all the serial numbers up to 10 under the active cell.

⧭ For convenience, we demonstrate methods using a couple of rows. Feel free to use as many rows as you need to test or work with the described methods.

Download Excel Workbook