Auto Generate Serial Number in Excel VBA (4 Ways)

Typical Excel worksheets contain hundreds of rows, and it’s tedious to insert serial numbers for those rows manually. Excel VBA Macros are strong tools. Users can auto generate serial number in Excel VBA easily.

Let’s say we have Customer Credentials, and we want to insert serial numbers adjacent to those entries.

In this article, we demonstrate multiple variants of VBA Macros to auto generate serial number in Excel VBA.

Auto Generate Serial Number in Excel VBA: 4 Macro Variants

There are multiple variants of VBA Macros users can use any of them to achieve the outcome.

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

The dataset contains no serial numbers. So, add or insert a Serial Number column adjacent to the range as shown in the following image.

Step 1: Press CTRL+11 to first open Microsoft Visual Basic then click on Insert > Module to insert a 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

➤ in the code,

1 – start the macro procedure by declaring the Sub name. You can assign any name to the code.

2 – 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.

Step 2: Press F5 to run the macro and return to the active worksheet. You see, Excel inserts the serial numbers in the inserted column depending on its neighbouring column’s non-blank status.

Method 2: Generating Dynamic Serial Number Using VBA in Excel

Sometimes, users need to generate dynamic or automatic serial numbers as they go by inserting entries into other adjacent cells. In those cases, this macro variant allows users to insert dynamic serial numbers with each entry.

Suppose users don’t have any content in their respective cells, as shown in the image below.

Step 1: Launch Microsoft Visual Basic then double-click on any Sheet to display the Worksheet’s Code window. Type 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,

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.

Step 2: After inserting the macro, return to the worksheet and keep typing Customer Names. Excel automatically inserts the serial number with formula as you hit ENTER as depicted in the following image.

🔺 As you go on, Excel keeps inserting the serial numbers no matter how many rows you populate.

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

Often, users use alphanumeric characters as serial numbers to identify them as certain groups. In that case a fixed number (i.e., 5, 6, or anything) of characters is used. If a sample serial number is inserted in a cell a VBA Macro can generate an auto serial number by mimicking it. Users just have to click on the Macro Button every time they want to assign a serial number. The whole situation is represented in the below picture.

Let’s follow the instructions below to generate an auto number sequence in Excel using VBA macros.

Step 1: 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

➤ From the above image, the code’s sections,

1 – declare the macro and assign the variable.

2 – insert the serial number using a VBA WITH statement.

3 – assign values to the variable. The TextChar fetches the left 2 text characters using the LEFT function. And the RIGHT function brings the number characters.

4– Execute a condition using the VBA IF function. The condition is to put zeros in case serial numbers don’t add up to a total 5 number characters. The LEN function counts the number of characters.

Step 2: Now, in the worksheet, click on the Macro Button to insert serial numbers one at a time. Each clicking on the macro button runs the macro and Excel inserts a serial number as shown below.

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

In cases users may want to restrict the serial number insertion up to a value (i.e., X). In this method, we demonstrate active cell serial number insertion up to a value.

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

Step 2: Insert a Module in Microsoft Visual Basic Window then write 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

➤ The code is marked in parts,

1 – begin 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.

Step 3: 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.

🔺 In a moment, 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

Conclusion

This article demonstrates multiple variants of VBA Macros to auto generate serial numbers in Excel VBA. Users can use any of the methods depending on their data types. We hope this article provides convenient methods to work with. Comment if you have further inquiries or have anything to add.