Adding a Unique Random Number within a Range

With the assistance of 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 onlyappeared once and will not repeat itself.  

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