Excel VBA generate non-duplicated random number

This Excel VBA tutorial explains how to generate non-duplicated random number using Excel VBA.

You may also want to read:

Generate random letter A to Z

Generate random HKID Hong Kong ID

Excel VBA generate non-duplicated random number

In my previous post, I demonstrated how to assign random number to a value using Rank Function and Rand Function. In this post I will introduce a more direct approach to assign a random number with the help of VBA.

In this approach, each time after a random number is generated, VBA checks whether specific range already has the number. If it does, then regenerate another random number until the number is not repeated.  The probability of generating some random numbers are dependent of previous numbers, this has statistics implication if you use it for simple random sampling.

VBA Code to generate non-duplicated random number

Click here to learn how to use Rnd Function to generate random number with defined lowerbound and upperbound.

Public Sub generateRandNum()
    'Define your variabiles
    lowerbound = 1
    upperbound = 20000
    Set randomrange = Range("A1:C5000")
    
    randomrange.Clear
    For Each rng1 In randomrange
        counter = counter + 1
    Next
    
    If counter > upperbound - lowerbound + 1 Then
        MsgBox ("Number of cells > number of unique random numbers")
        Exit Sub
    End If
    
    For Each Rng In randomrange
        randnum = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
        Do While Application.WorksheetFunction.CountIf(randomrange, randnum) >= 1
            randnum = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
        Loop
        Rng.Value = randnum
    Next
End Sub

Example – generate non-duplicated random number

Before running the above VBA, the first thing is to define the Excel Range in which you want to generate random number. You can generate multiple rows and columns. Second, define the upper range of lower range of the random number.

For example, I want to generate 10 random numbers in Range A1:A10, then you should define your variables as

lowerbound = 1
upperbound = 10
randomrange = "A1:A10"

Run the Macro and you get the below result

excel non-duplicated random number 01

If you want to generate 10 random numbers but the range is defined as A1:A9, an error message will pop up because it doesn’t make sense to generate 9 unique numbers in 10 Cells.

excel non-duplicated random number 02

If you don’t need the numbers to be unique, then you should simply use RandBetween Function for each Cell in A1:A10 but you will get duplicates.

excel non-duplicated random number 03

It is also possible to use non-VBA solutions to generate non-duplicated random number, click here to learn more.

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

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