Generate random non-prime numbers


Well-known Member
Apr 8, 2002
I want to be able to run a macro that will generate in cells A2:B21 random non-prime numbers between 2 and 400. Can this be done?

Excel Facts

Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
I am teaching my daughter maths and have created worksheets to test adding, subtraction and multiplication. I want to produce a sheet to do division and need non-prime numbers so that they are divisible.
Upvote 0

Preselect a "x" Rows by 1 Column range. Then, in the formula bar type

(I chose 8 samples from the range 1 to 30)

=NonPrimeSampleNoReplace(8, 1, 30)

Array enter this worksheet array by hitting Ctrl+Shift simultaneously with Enter.

The Folllowing UDFs are used. This has been lightly tested, so please post back with any errors you encounter.

Rich (BB code):
Function IsPrime2(Number As Double) As Variant
Dim x As Long, dbl As Double, lngUp As Long

''''    Author Dave Braden et al, posted to MS Excel Newsgroups
''''    off&

If Number <> Int(Number) Then
    IsPrime2 = CVErr(xlErrValue)
    Number = Abs(Number)
    If Number < 2 Then
        IsPrime2 = False
    ElseIf Number / 2 = Int(Number / 2) Then
        IsPrime2 = Number = 2
    ElseIf Number < 9 Then
        IsPrime2 = True
        lngUp = Int(Sqr(Number))
        IsPrime2 = True
        For x = 3 To lngUp Step 2
            dbl = Number / x
            If dbl = Int(dbl) Then
                IsPrime2 = False
                Exit For
            End If
        Next x
    End If
End If
End Function

Function NonPrimeSampleNoReplace(SampleSize As Long, LowerBound As Long, _
UpperBound As Long, Optional AlreadyUsedArray)

''''  Author:  Jay Petrulis

Dim PopulationCollection As New Collection
Dim SampleArray() As Long
Dim temp As Long
Dim i As Long
Dim j As Long


If UpperBound < LowerBound Then
    temp = UpperBound
    UpperBound = LowerBound
    LowerBound = temp
End If

If (SampleSize > (UpperBound - LowerBound + 1) Or SampleSize <= 0) Then
    NonPrimeSampleNoReplace = CVErr(xlErrValue)
    Exit Function
End If

ReDim SampleArray(1 To SampleSize) As Long

For i = LowerBound To UpperBound
    If IsMissing(AlreadyUsedArray) Then
        If Not IsPrime2(CDbl(i)) Then
            PopulationCollection.Add i
        End If
        If IsError(Application.Match(i, AlreadyUsedArray, 0)) Then
            If Not IsPrime2(CDbl(i)) Then
                PopulationCollection.Add i
            End If
        End If
    End If
Next i

For i = 1 To SampleSize
    With PopulationCollection
        j = Int(Rnd * .Count) + 1
        SampleArray(i) = .Item(j)
        .Remove j
    End With
Next i

    Set PopulationCollection = Nothing
    NonPrimeSampleNoReplace = Application.Transpose(SampleArray)
End Function
Upvote 0
Hi Jay,
Thanks for that. I had a problem selecting the whole range first because that created an array that I could not edit. However, I got round it by selecting the first cell, Ctrl+Shift+Enter and autofilling down. I have managed to do what I set out to and my daughter is amazed!

Many thanks
Ian :biggrin:
Upvote 0

If you resample with your method, you may get duplicate entries.

Create the worksheet array, and then when you want new items, highlight the range again, hit the F2 key, and then Ctrl+Shift+Enter. You will get a new data set.

Also, if you want to continue in other cells, but don't want any repeats, there is an optional 4th argument. Put the exiting range as the optional 4th argument and the existing entries will be excluded from the next set as well.
Upvote 0
The following procedure will :
1. Put random numbers in the range A2:b21
2. clear out old numbers from the indicated range
3. Make sure all numbers are unique
4. make sure none of the numbers are Prime

Public Sub RandNum()
For Each c In Range("A2:B21")
TryAgain: c.Value = (Int((99 * Rnd) + 1))
If Application.WorksheetFunction.CountIf(Range("A2:B21"), c.Value) > 1 Or _
IsPrimeNumber(c.Value) Then GoTo TryAgain
Next c
End Sub
Public Function IsPrimeNumber(Num) As Boolean
IsPrimeNumber = True
M = Application.WorksheetFunction.Ceiling(Sqr(Num), 1)
Do While M - cnt > 1
On Error Resume Next
If (Num Mod (M - cnt)) = 0 Then IsPrimeNumber = False
cnt = cnt + 1
If Num > 3 Then
If (Num Mod 3) = 0 Then IsPrimeNumber = False
If (Num Mod 2) = 0 Then IsPrimeNumber = False
If Num = 2 Then IsPrimeNumber = True
End If
End Function

1. Select the sheet you want code to work in
2. right click the the "Name Tab" of sheet
3. Select "View Code" in drop down menu
4. VBE window will open ... paste code in and exit VBE

1.While the sheet to evaluate is active goto tool bar
2. ON tool bar goto "Tools...Macro....Macros"
3. A list of Macros should appear
4. To run the above Macro Double Click on it's name in the list
Upvote 0
Let me add a solution although it doesnt meet exactly what you wanted (not includes 2 and has repeats)

But it is simple to handle.

Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back