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:
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:
If it’s not, then enable it in the Customize Ribbon section of Excel’s Options dialog:
Once the Developer bar is enabled, start to create a new macro by clicking on the Record Macro button and fill in the fields:
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:
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!
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:
/Emil
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
@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 valueThe 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
Hi, @Dipesh,
You can try to change the cell format of the column to something like this:
Hope this helps,
Emil
Hi @ Emil
I am not much familiar with excel can u please explain me how to add prefix to my id.
Regards
Sathya
@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
I’d like the macro to run and fill each row that has got data with a number and stop at a empty cell
@Barry Mahbouby, I don’t have a macro doing that. It’s probably easier to copy the values manually (Ctrl + Down arrow moves you to the next logical row block limit which could be useful in this case).
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.
Thanks! Great guide
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?