Unique Random Number In Ascending Order

Hi.
I need to generate repeat NOT REPEATED numbers and arrange the numbers in ascending order. If anyone can fix I would appreciate it.

Code:

---

Sub RndNumb()
With Range("A2:A9")
    .Formula = "=INT(99*RAND()+1)"
    .Value = .Value
End With
End Sub

---

Here's one way to do it....

Code:

---

Sub GenerateRandomUnique()
   
    Dim lng As Long
    Dim var As Variant
    With CreateObject("Scripting.Dictionary")
        Do While .Count <= Range("A2:A9").Cells.Count
            lng = Rnd * 99 + 1
            .Item(lng) = Empty
        Loop
        var = Application.Transpose(.Keys)
        SortIntegerArray var
        Range("A2:A9").Value = var
    End With
   
End Sub
Sub SortIntegerArray(ByRef paintArray As Variant)
    Dim lngX As Long
    Dim lngY As Long
    Dim intTemp
   
    For lngX = LBound(paintArray) To (UBound(paintArray) - 1)
        For lngY = LBound(paintArray) To (UBound(paintArray) - 1)
            If Val(paintArray(lngY, 1)) > Val(paintArray(lngY + 1, 1)) Then
                'exchange the items
                intTemp = paintArray(lngY, 1)
                paintArray(lngY, 1) = paintArray(lngY + 1, 1)
                paintArray(lngY + 1, 1) = intTemp
            End If
        Next
    Next
End Sub

---

Hi.

Great solution!!!

Thank you very mutch!!!

Hi.
how do I use this code in the range "A2: I2"?
As I mark this post as solved?

you should like to see the discussion here on this topic

Get Random List : « excelpoweruser
Get Random List : | LinkedIn

Rajan

Try this

Code:

---

Sub GenerateRandomUnique()
   
    Dim lng As Long
    Dim var As Variant
    Dim rng As Range
    Set rng = Range("A2:A9")
    With CreateObject("Scripting.Dictionary")
        Do While .Count <= rng.Cells.Count
            lng = Rnd * 99 + 1
            .Item(lng) = Empty
        Loop
        var = Application.Transpose(.Keys)
        SortIntegerArray var
        If rng.Columns.Count = 1 Then
            rng.Value = var
        ElseIf rng.Rows.Count = 1 Then
            rng.Value = Application.Transpose(var)
        End If
    End With
   
End Sub

---

Hi.
In this case it would be the range "A2: I12" where numbers can not be repeated.

65 58 28 6 39 49 89 26 31
80 24 7 75 37 66 42 69 3
48 9 8 32 51 63 67 11 45
10 0 18 85 4 50 64 88 44
38 81 93 1 27 41 5 16 73
76 82 21 92 35 23 61 13 46
86 74 56 96 71 29 47 33 43
15 36 97 91 59 14 77 72 20
68 87 79 62 83 17 19 22 84
53 34 95 57 12 25 55 40 54
70 98 30 90 60 52 2 94 78

Hi

you can try this UDF.

Code:

---

Option Explicit
Function RANDOMNUMGENERATOR(ByVal MinVal As Long, ByVal MaxVal As Long, _
            Optional HowMany As Long, Optional ByVal UNIQUE As Boolean = True)

    '// Created by  : Krishnakumar @ ExcelFox.com
   
    Dim Diff As Long, Tot  As Long
    Dim RNG() As Long, n    As Long
    Dim AC, RowsCount      As Long
    Dim tmp, ColCount      As Long
    Dim r As Long, c        As Long
   
   
    On Error Resume Next
    Set AC = Application.Caller
    If Err.Number <> 0 Then
        Err.Clear: On Error GoTo 0: GoTo 2:
    End If
    On Error GoTo 0
   
    If TypeName(AC) <> "Range" Then
        RANDOMNUMGENERATOR = CVErr(xlErrRef)
        Exit Function
    End If
   
    Application.Volatile
   
    RowsCount = AC.Rows.Count
    ColCount = AC.Columns.Count
    Tot = RowsCount * ColCount
    If HowMany <> Tot Then HowMany = Tot
   
    ReDim RNG(1 To RowsCount, 1 To ColCount)
   
2:
    Diff = MaxVal - MinVal
    RANDOMNUMGENERATOR = Empty
    If UNIQUE Then
        With CreateObject("scripting.dictionary")
            Do While .Count <= HowMany - 1
                Randomize
                .Item(MinVal + Int(Rnd * Diff)) = Empty
            Loop
            tmp = .keys
            For r = 1 To RowsCount
                For c = 1 To ColCount
                    RNG(r, c) = tmp(n)
                    n = n + 1
                Next
            Next
            RANDOMNUMGENERATOR = RNG 'Application.Transpose(.keys)
        End With
    Else
        For r = 1 To RowsCount
            For c = 1 To ColCount
                RNG(r, c) = MinVal + Int(Rnd * Diff)
            Next
        Next
        RANDOMNUMGENERATOR = RNG
    End If
   
End Function

---

select A2:I12, type =RANDOMNUMGENERATOR(1,100)

It's an array formula. Confirmed with CTRL + SHIFT + ENTER

Hi.

Thank you all for helping me, it was great!!