Excel VBA code to generate serial numbers

serial-number-generator

Why serial numbers?

Serial number plays a significant role in organizing the data, You may ask? excel has its own serial numbers in the left, but when it comes to the hardcopy of your data excel's serial numbers will not come together. That is why we use our own serial numbers in a separate column.

Now we know the importance of adding serial numbers, but how are we going to add if our data is huge say 5000 rows of data, obviously it is difficult if you use some shortcuts also. That is where macros play a vital role in making life easier.

What does this code do?

Nevertheless the size of data this macro prints serial numbers in a fraction of time. The main advantage of this macro is that it creates serial numbers dynamically by getting the number of rows from the user and prints numbers accordingly.

There are many ways to add serial numbers in excel, but now we will see how to add serial numbers using VBA.

Adding vertically


First, we declare required variables and with the help of input box, we get the number of rows as an input from the user and save that value in the "req" variable.

Second, with the help of for next loop, we print the serial numbers starting from 1 to user's input that is saved in "req" variable i.e "For i = 1 To req".

Option Explicit
Sub Serialnumber()
Dim req As Variant
Dim i As Integer
req = InputBox("Any Description", "Any Title", 10)
On Error GoTo cancelled
For i = 1 To req
Cells(i, ActiveCell.Column).Value = i
Next i
cancelled:
End Sub

Once you run the above code you will see an input box popup, enter the number of rows to generate serial numbers. In this example, the number of rows is set to 10.

serial-number-generator

Output

serial-number-generator

Adding Horizontally

This is the same as adding vertically instead of "Cells(i, ActiveCell.Column). Value = i" which we used for vertical, to make it horizontal we should use Cells(ActiveCell.Row, i).Value = i.

Option Explicit
Sub Serialnumber()
Dim req As Variant
Dim i As Integer
req = InputBox("Any Description", "Any Title", 10)
On Error GoTo cancelled
For i = 1 To req
Cells(ActiveCell.Row, i).Value = i
Next i
cancelled:
End Sub

Once you run the above code you will see an input box popup, enter the number of columns to generate serial numbers. In this example, the number of columns is set to 10.

serial-number-generator

Output

serial-number-generator

As you can see we achieved what we want, the code did it right. The project source code file is available for download you can download it from the below link. Try experimenting with this code to get clarity.

Hope you liked this article, if so please share it with your friends.

Try experimenting with these codes to get clarity.

Excel Logo

Download project file

Video: Excel VBA code to generate serial numbers

Share on:
Most Popular