Random Numbers Generator without Repeat Numbers using VBA in Microsoft Excel 2013

VBA Distinct Random Number Generator

If you want to generate random number in Excel & all the numbers should be unique (no repeat), you can use Excel Rand function to generate random numbers.

In this article, we will learn how to quickly create list of unique random numbers

We need to follow the below steps:

• Click on Developer tab
• From Code group, select Visual Basic

• Enter the following code in the worksheet module

Function DistinctRandomNumbers(NumCount As Long, LLimit As Long, ULimit As Long) As Variant
Dim RandColl As Collection, i As Long, varTemp() As Long
DistinctRandomNumbers = False
    If NumCount< 1 Then Exit Function
    If LLimit>ULimit Then Exit Function
    If NumCount> (ULimit - LLimit + 1) Then Exit Function
    Set RandColl = New Collection
    Randomize
    Do
        On Error Resume Next
i = CLng(Rnd * (ULimit - LLimit) + LLimit)
RandColl.Addi, CStr(i)
        On Error GoTo 0
    Loop Until RandColl.Count = NumCount
ReDimvarTemp(1 To NumCount)
    For i = 1 ToNumCount
varTemp(i) = RandColl(i)
    Next i
    Set RandColl = Nothing
DistinctRandomNumbers = varTemp
    Erase varTemp
End Function
 
Sub Test()
Dim varrRandomNumberList As Variant
varrRandomNumberList = DistinctRandomNumbers(50, 1, 100)
Range(Cells(3, 1), Cells(50 + 2, 1)).Value = _
Application.Transpose(varrRandomNumberList)
End Sub

 

• After copying the code, you need to type the formula in any cell
• =DistinctRandomNumbers(50, 1, 100)

In this way, we can make a list of distinct random numbers without repetition in Excel 2013.

Another way in Excel 2016

RANDBETWEEN function stands for Random number between the 2 numbers. To get the same, follow the below steps.

RANDBETWEEN function returns the random numbers between any two numbers.

Syntax:

Bottom : The smaller of the 2 numbers.

Top : The larger of the 2 numbers.

As we know a phone number have the following condition:

1. It has 10 digits
2. Cannot starts with 0

So the smallest possible number comes out to be 1000000000 and the largest possible number comes out to be 9999999999

So use the formula shown below to generate phone numbers

Use the Formula:

1000000000 : smaller one

9999999999 : larger one

Just enter the formula once and repeat results using the drag down option in excel as shown in the above snapshot.

As you can see, you can get the random phone numbers in Excel.

Another ways to pick random values from given list

For this, we can opt for any of the two methods mentioned below. One method is using CHOOSE function. In this We need to input all individual values from the list to the function. You can use this formula for when number values in the list are less.

Formula using CHOOSE function:

n : number of values in list

value1 , value2 , value3 , till value n : individual values from the list. These values can be input either using the cell reference or using directly with quotes or using the cell reference.

Another method is using the INDEX function where we will feed a random number as index which will result in the corresponding value at the index.

Formula using INDEX function:

data : data array can be a list or table.

1 : start index

col_index : column index to point out the list in table.

Notes :

1. The function returns an error, if the first number argument ( bottom ) is larger than the second number argument ( top ).
2. The function returns error, if the argument to the function is non - numeric.

Hope this article about Random Numbers Generator without Repeat Numbers using VBA in Microsoft Excel is explanatory. Find more articles on generating random numbers or date values here. If you liked our blogs, share it with your fristarts on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

Download - Random Number Generator without Repeat Numbers-xlsm

Related Articles :

Excel VBA Variable Scope : In all the programming languages, we have variable access specifiers that define from where a defined variable can be accessed. Excel VBA is no Exception. VBA too has scope specifiers.

All About Excel Named Ranges : excel ranges that are tagged with names are easy to use in excel formulas. Learn all about it here.

Relative and Absolute Reference in Excel : Understanding of Relative and Absolute Reference in Excel is very important to work effectively on Excel. Relative and Absolute referencing of cells and ranges.

Generate Random Phone Numbers : Generate random 10 digti numbers using the RANDBETWEEN formula in Excel

Get Random number From Fixed Options : Generate random numbers form the list having criteria in Excel.

Get Random numbers between two numbers : RANDBETWEEN function generator number between the two given numbers in Excel.

Excel Random Selection: How to Get Random Sample From a Dataset : Use the random samples in Excel for the explained examples here.

How to use the RANDBETWEEN Function in Excel : Excel RANDBETWEEN function returns a number between two given numbers in Excel.

Popular Articles :

50 Excel Shortcuts to Increase Your Productivity : Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

How to use the IF Function in Excel : The IF statement in Excel checks the condition and returns a specific value if the condition is TRUE or returns another specific value if FALSE.

How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

How to Use SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.

How to use the COUNTIF Function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.