How To Generate Serial Number In Excel Vba

  1. Auto Number In Excel

I was going through the questions on the Microsoft Excel Community, and one of the questions was on generating random numbers in Excel between 1 to 10, where none of the numbers repeat.

VBA Tip: Random Number Generator. You can use the following code to generate a random number between 1 and 50: Sub randomnum 'Initialize the random number generator. '= Randomize: add this before you call the Rnd function to obtain completely random values.

My first instinct was to use the RANDBETWEEN function. I did a quick check in Excel, and it failed me – there were repetitions in the results.

Here is what I got:

So I had to resort to the RAND function.

It works well with a negligible probability of the numbers repeating (I tested it multiple times on more than 100,000 cells, and there were no repetitions. If you are generating a lot of random numbers, you can test it once).

Auto Number In Excel

Generating a Set of Unique Random Numbers in Excel

Here is how you can use the RAND function to generate a set of unique random numbers in Excel:

  1. In a column, use =RAND() formula to generate a set of random numbers between 0 and 1.
  2. Once you have generated the random numbers, convert it into values, so that it won’t recalculate again and again to make your workbook slow.
  3. In the adjacent column, use the following RANK formula and copy/drag for other cells (here I have used it for 10 numbers only, hence A2:A11. You can adjust the range accordingly).
    =RANK(A2,$A$2:$A$11)
  4. That’s it. You would have a list of random numbers from 1 to 10, and none of the numbers would repeat.

NOTE: Remember to convert cells with RAND function from formula to values, else it will recalculate and change the list of Random Numbers every time you change anything in the workbook.

Caution:While I checked and didn’t find repetitions in the result of the RAND function, I still recommend you check once you have generated these numbers. You can use Conditional Formatting to highlight duplicates or use the Remove Duplicate option to get rid of it.
How to generate serial number in excel vba pdf

Do you use any other technique to do this? Let me know in the comments section.

You May Also Like the Following Excel Tutorials:

  • Automatically Sort Data in Alphabetical Order using Formula.
  • Random Group Generator Template.

Session expired

Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.