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 the 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 the last row then copy-paste the below formula in Cell A2 and drag it down till the last row

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

And if you want to apply only a single formula to insert serial numbers based on the criteria in the next column then you can apply the below formula subject you are using the latest version of Ms- Excel.

The below formula is used to insert the serial numbers in Column A if Column B has some text

=SEQUENCE(COUNTIF(B:B,"*")-1,1)

Following are the steps to apply serial numbers using VBA code.

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

Open chat
Talentnett Solutions
Hello,

How may I help you today!

Regards,
Umesh Agarwal
Contact - +91 - 7017544508
Email - umesh.agarwal@talentnett.com

Cool Tips and Tricks Directly Delivered in your Inbox. Subscribe to our Newsletter.

You have successfully subscribed to the newsletter

There was an error while trying to send your request. Please try again.

Talentnett will use the information you provide on this form to be in touch with you and to provide updates and marketing.