How to Insert serial numbers automatically in excel using VBA

How to Insert serial numbers automatically in excel using VBA

Indexing or Sequencing is a crucial part when you work with Excel. You often apply manual indexing or insert a formula manually which takes a lot of time. This article will give you a solution on how to insert serial numbers automatically in excel using VBA.

Tip:

If you do not wish to apply VBA code then you can simply apply ROWS formula and fill it down to the last row of the dataset.

For example: if you want to insert serial numbers in Column A from Row two to last row then copy-paste the below formula in Cell A2 and drag it down till the last row

Formula:
=ROWS($A$2:A2)

Step 1 – Open an Excel sheet or Create one

Step 2 – Go to Visual Basic Editor or Press Alt+F11

Step 3 – Go to Insert>>Module

VBA Code

Option Explicit
        
Sub Sno_()
        
    Dim cl As Range
    Dim rng As String
    Dim rng1 As Long
    Dim lrow As Long
    Dim get_colName As String
            
    On Error Resume Next
        
    Set cl = Application.InputBox(Title:="Range for SNo.", _
                 Prompt:="Select the First Row of the Column where you want to put Serial Numbers", _
                 Type:=8)
        
    rng = cl.Address
    rng1 = Range(rng).Offset(0, 1).Column
    get_colName = Left(rng, 2)
        
    lrow = Cells(Rows.Count, rng1).End(xlUp).Row
        
    cl.Formula = "=Rows(" & rng & ":" & Replace(rng, "$", "") & ")"
    Range(rng & ":" & get_colName & lrow).FillDown
             
End Sub

Step 4 – Paste the above code in the module window (refer below image)

Step 5 – Go back to your datasheet or press Alt+Q

Step 6 – Go Insert>>Shapes

Step 7 – Assign the same Macro to that Shape (refer below image)

Step 8 – Click on the shape and select the first row where you want to apply serial numbers or indexing and then press ok

Note: This is code meant to insert serial numbers on the left side only, if there is no data on the immediate right column then it will not work.

Hope it Helps!

Read More:

How to unmerge specific cells in Excel using VBA

How to extract text from a bracket cell in excel

How to convert multiple text values to a date format using a single formula

COMMENTS