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.

Dataset-Auto Generate Serial Number in Excel VBA

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


Download Excel Workbook


4 Macro Variants to Auto Generate Serial Number in Excel VBA

There are multiple variants of VBA Macros users can use any of them to achieve the outcome. Also go through This Article to learn more about Module and Sheet’s Code insertion in Microsoft Visual Basic.


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.

Inserted Serial Number-Auto Generate Serial Number in Excel VBA

Step 1: Follow This Link or 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

Macro

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 neighboring column’s non-blank status.

Outcome

Read More: Auto Serial Number in Excel Based on Another Column


Method 2: Generating Dynamic Serial Number Using VBA in Excel

Sometimes, users need to generate dynamic 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.

Dynamic Serial Number-Auto Generate Serial Number in Excel VBA

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

Macro

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 as you hit ENTER as depicted in the following image.

Dynamic Serial Number

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

Outcome

Read More: How to Add Numbers 1 2 3 in Excel (2 Suitable Cases)


Similar Readings


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.

Macro Button-Auto Generate Serial Number in Excel VBA

Step 1: Follow This Link to 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

Macro

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.

Outcome

Read More: How to Create a Number Sequence with Text in Excel (5 Methods)


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).

Up to X-Auto Generate Serial Number in Excel VBA

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

Macro

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.

Input box

🔺 In a moment, Excel inserts all the serial numbers up to 10 under the active cell.

Outcome-Auto Generate Serial Number in Excel VBA

Read More: How to Increment Row Number in Excel Formula (6 Handy Ways)


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.


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.

Have a quick visit to our amazing website and check out our recent articles on Excel. Happy Excelling.


Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo