Month: May 2021

In this article, you will see Excel VBA to generate random unique numbers and VBA UDF (User Defined Function) for generating random numbers list without repeats. 

Unique random numbers generator VBA

In the below GIF you can see Unique (without duplicates) random numbers list getting generated based on information in the activecell, after pressing the shortcut key.

Below is the VBA code for generating unique random numbers in an Excel sheet.

To use the below VBA you just have to paste the code in the standard module of VBA IDE.

How to insert and run VBA code in Excel?

Sub genUnqRnd_NumbersList()
'learnyouandme.com
  Dim infoStr As String, ar As Variant
  Dim minNum As Long, mxNum As Long
  Dim listSz As Long
''=sec1 s==
  infoStr = ActiveCell.Value
  ar = Split(infoStr, ",")
  If UBound(ar) <> 2 Then
    MsgBox "Please supply all the information or inputs"
    Exit Sub
  End If
  minNum = CInt(ar(0)): mxNum = CInt(ar(1)): listSz = CInt(ar(2))
  If listSz > mxNum - minNum + 1 Then listSz = mxNum - minNum + 1
''=sec1 e==
''=sec2 s==
  Dim col As Collection, i As Long
  Dim rndNum As Long
  Set col = New Collection
  i = 1
  Do While i <= listSz
    Randomize
    rndNum = (mxNum - minNum + 1) * Rnd + minNum
    On Error Resume Next
      col.Add rndNum, CStr(rndNum)
      If Err.Number = 0 Then i = i + 1
    On Error GoTo 0
  Loop
''=sec2 e==
''=sec3 s==
Dim rndUnqAr() As Variant
ReDim Preserve rndUnqAr(1 To listSz)
For i = 1 To listSz
  rndUnqAr(i) = col(i)
Next i
ActiveCell.Offset(1).Resize(listSz, 1).Value = Application.Transpose(rndUnqAr)
''=sec3 e==
End Sub

In a cell write information like From Number, To Number and How many unquie numbers you want separated by comma. 

Example:- If you want to generate a unique random list of 10 values starting from 10 to 50 then write 10,50,10.

Now keep this information cell active and run the code. 

Random numbers will get generated from the below cell of the active cell.

Unique random numbers list VBA UDF

You can also generate a list of unique random numbers using VBA UDF (User Defined Function).

Function genUnqRndNumbersList takes three inputs

1st is from number

2nd is to number

3rd is the count of numbers in the list

Open VBA IDE by pressing ALT+F11 and create a new standard module and paste the below VBA code.

After pasting the function in the standard module you are ready to use the function in a cell.

Learn more about Creating VBA UDF functions

Video link Create custom / User Defined Functions or UDF in Excel using VBA.

Function genUnqRndNumbersList(minNum As Long, mxNum As Long, listSz As Long) As Long()
'learnyouandme.com
 Application.Volatile
  Dim ar As Variant
  If listSz > mxNum - minNum + 1 Then listSz = mxNum - minNum + 1
''=sec1 s==
  Dim col As Collection, i As Long
  Dim rndNum As Long
  Set col = New Collection
  i = 1
  Do While i <= listSz
    Randomize
    rndNum = (mxNum - minNum + 1) * Rnd + minNum
    On Error Resume Next
      col.Add rndNum, CStr(rndNum)
      If Err.Number = 0 Then i = i + 1
    On Error GoTo 0
  Loop
''=sec1 e==
''=sec2 s==
Dim rndUnqAr() As Long
ReDim Preserve rndUnqAr(1 To listSz, 1 To 1)
For i = 1 To listSz
  rndUnqAr(i, 1) = col(i)
Next i
genUnqRndNumbersList = rndUnqAr
''=sec2 e==
End Function

To output a unique random numbers list you have to make this formula an array one.

Select a vertical range then write a formula in the first cell of the selected range.

Fill the required inputs then press CTRL+SHIFT+ENTER to fill down array formulas in selected cells.