Excel macro for generating sequential id numbers

This post describes how to write a simple Excel macro to create sequential id numbers. This can be useful whenever you create lists with rows that should have a unique id, for example in product backlogs, requirement lists, etc. Often you type these ids by hand, the first starting with 1 and then increasing the id for each consecutive row. This works okay as long as the rows are in the original order but when sorting and filtering is used it can be a little difficult to know what the next row id should be. You can always select all existing ids and look at Excel’s status row which is the highest but it would be nice to automate this, yes? Yes! 🙂

This is the result we’re aiming for:

Generated id rows

To meet this goal, I have created a simple Excel macro that calculates the next id and this post describes how to use it.

First of all, make sure that the Developer Tab is enabled in the Ribbon:

Excel Ribbon Developer Tab

If it’s not, then enable it in the Customize Ribbon section of Excel’s Options dialog:

Enable Excel Developer Tab In Ribbon

Once the Developer bar is enabled, start to create a new macro by clicking on the Record Macro button and fill in the fields:

Create macro

Close the dialog and immediately stop recording the macro by clicking the same button again (it now says Stop Recording).

Then click the Macros button and edit the newly created macro by clicking the Edit button:

Edit macro

The empty source code for the new macro is displayed. Paste the following code into the macro:

ActiveCell.Formula = "=MAX(A4:A" + CStr(ActiveCell.Row - 1) + ")+1"
ActiveCell.Formula = ActiveCell.Value 'Convert formula to a value

Adjust the area so it suits the table. In the example the id column is A and the first row with data is row 4, so the area is given as A4:A. Close the macro dialog and you’re done!

Edit macro text

Try the macro by placing the cursor in the id column of the first empty row and press the keyboard shortcut you selected when you created the macro (Ctrl-N in my case) and the new id should turn up. No more counting of rows or selecting all rows to see what the highest number is, we have now automated this process, and again there is some rest to be had for our lazy soul… 😉

Note that the Excel workbook must be saved with the .xlsm extension (Excel Macro-Enabled Workbook) for security reasons as macros are considered riskful by Microsoft:

Save Macro Enabled Workbook

/Emil

11 thoughts on “Excel macro for generating sequential id numbers”

  1. Hello, this is great help by the way!

    Quick question though…is there a way to make the macro above insert a new line before it adds a sequential number (so new products are always at the top)?

    I know like nothing about macros and was trying to fiddle around with combining codes I found. I got to the point where I could add a row but it would only add “0” for each new line.

    Here’s that code.

    Sub AddLine()

    ‘ AddLineAndHopefullyID Macro


    Rows(“4:4”).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveCell.Formula = “=MAX(A4:A” + CStr(ActiveCell.Row – 1) + “)+1”
    ActiveCell.Formula = ActiveCell.Value ‘Convert formula to a value
    End Sub

    Thanks for any help!
    Jens

  2. @Jens

    Here’s a rather late solution that seems to work for me:

        'Insert row
        ActiveCell.EntireRow.Select
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        
        'Create formula and store value in cell
        ActiveCell.Formula = "=MAX(A" + CStr(ActiveCell.Row + 1) + ":A99999)+1"
        ActiveCell.Formula = ActiveCell.Value 'Convert formula to a value
    
  3. The formula works fine, just a query if I want to prefix the unique id with the text like “SF000”, is it possible?

    Many Thanks
    Dipesh Shah

  4. Hi @ Emil

    I am not much familiar with excel can u please explain me how to add prefix to my id.

    Regards
    Sathya

  5. @Sathya, this is how to change the format:

    1 Select the cell
    2 Open the cell format dropdown in the toolbar
    3 Select “More Number Formats…
    4 In the dialog that is opened, select “Custom
    5 Type the format specification, “SF”00000 in the field marked “Type:”

    Done!

    Hope this helps.

    Emil

  6. I want my first column i.e A as serial number starting from 1,2,3,4….(goes on increasing) till the value present in the column B . I want a vba code for this. I am writing my vba code in the outlook vba.
    Please help ASAP.

  7. Hi! I’m using the Macro above described to sequence numbers, however, I’m also using a Macro to remove some of these items once a condition is met. My problem lies where:
    I have all the sequences on Column A. At the moment the oldest one is 20082375 and the macro works well to create a new number based on the above, however, what happens is, if 20082375 meets the condition and goes to Sheet2, then the new invoice number that comes up is 20082375 as it doesn’t exist in the Sheet. Basically, I need to not repeat any number that has been already created. Does it make sense?

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.

This site uses Akismet to reduce spam. Learn how your comment data is processed.