Hello Excellers; time for another blog post in my #MacroMonday blog post in my #Excel series. Do you need a random number generator with no repeats for a specific project but don’t want to deal with the hassle of using an online tool or software? If so, then you’re in luck. A simple Excel macro can quickly generate random numbers within seconds. This tutorial will show you how to create and use the macro and provide tips on how to utilise it best. I use random numbers and data a lot in my work building spreadsheet solutions, mainly for testing purposes of dummy data. This powerful little Macro will generate random numbers with no repeats in a region of cells that we can specify and populate with one click of a mouse. Sounds good, right? So let’s get going!
What Does The Macro Do?
The Macro will use the RND function to generate a list of random numbers in a range of cells we select on the worksheet.
Starting The Macro.
First, you will need to open the Visual Basic Editor. There are two ways to do this. Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result. When you write an Excel macro, you need to choose where to store it. You have two choices.
- Store it in the current Excel workbook you are working on creating.
- Save the Macro in your Personal Macro workbook.
As I already know, this Macro is a handy one. I will store it in my Personal Macro workbook. I want to re-use this Macro, so it’s a keeper to generate random data anytime I need to. Feel free to download the code below.
Sub RandomNumNoDuplicates()
Set cellRange = Range(“A1:B20”)
cellRange.Clear
For Each Rng In cellRange
randomNumber = Rnd
Do While Application.WorksheetFunction.CountIf(cellRange, randomNumber) >= 1
randomNumber = Rnd
Loop
Rng.Value = randomNumber
Next
End Sub
2. Setting The Cell Range.
So, the first part of this Excel Macro is to set the cell range to fill with random numbers. In this example case study, I have decided to use the cell range A10:B20. This range of cells is initially cleared of any contents to allow the new random numbers to generate.
Set cellRange = Range(“A1:B20”)
cellRange.Clear
3. Using The Do While Loop.
The RND function inserts random numbers into the range. The Do While Loop is used in this instance and looks for the number in the predefined cell range (A1:B10). First, Excel checks to determine if the number already exists. The COUNTIF function checks if the random inserted number appears in the content already. This cool part of the code checks if the numbers in the range are indeed random. This loop continues until all cells in the range contain random numbers.
For Each Rng In cellRange
randomNumber = Rnd
Do While Application.WorksheetFunction.CountIf(cellRange, randomNumber) >= 1
randomNumber = Rnd
Loop
Rng.Value = randomNumber
Next
4. The Macro Ends.
Finally, once all cells in the specified range contain a random number the code ends. This End Sub line of code is inserted at the time the macro is named.
End Sub
In this blog post, we showed you how to create an excel macro that is a random number generator with no repeats or duplicates. We hope you found this tutorial helpful and easy to follow. Feel free to experiment with the code and customize it to fit your own needs. And don’t forget to let us know how it goes!
