Adding a Unique Random Number within a Range

With the assistance of Excel VBA you can add a unique random number between two data points.  For example, you could generate random numbers between 1 and 10 and list the sequence in an Excel range.  So every number only appeared once and will not repeat itself. This is very helpful when attempting to generate unique list to test conditions in Excel. The following is the procedure which needs to be placed in a regular module in VBA. The Excel file below is also a good starting point.

Option Explicit

Sub ProduceUniqRandom() 'Excel VBA to generate random number
Dim myStart As Long
Dim myEnd As Long
Dim i As Long
Dim a() 'Excel Dynamic Variable
Dim sh As Worksheet

Set sh=Sheet1
myStart=sh.[A2]
myEnd=sh.[b2]
ReDim a(0 To myEnd - myStart)

With Createobject("System.Collections.SortedList")
Randomize
For i=myStart To myEnd
.Item(Rnd)=i
Next i
For i=0 To .Count - 1
a(i)=.GetByIndex(i)
Next
End With
sh.Range("A5").Resize(UBound(a) + 1).Value=Application.Transpose(a)
End Sub