DMW logo
tel 01732 833085
tonbridge · kent · UK

Excel Macros to Fill Block of Cells with Random Numbers

How to code Excel VBA macros for filling blocks of cells with random numbers when numbers are needed to check the workings of a spreadsheet.

Last updated on 2020-05-26 by David Wallis.


Preface

Often when testing a new spreadsheet it’s necessary to fill blocks of cells with numbers. There are several techniques you can use to achieve this. And Excel can assist you: the RAND and RANDBETWEEN functions are useful in this respect.

Days were that I’d input a value, copy it down and then across to fill cells. Or would input a RAND formula and copy that down and across, and then copy and paste-special to freeze the numbers RAND had produced.

Bored with such repetition I’ve created the macro described here. It works for Excel versions 2007, 2010, 2013, 2016, 2019 and Microsoft® 365.


Random Number Fill Macro

Flight deck

The macro is dmwFillNumbers and its syntax and arguments are these:

dmwFillNumbers(rows&, cols&, LB&, UB&, decs&)

rows&number of rows in the cell block

cols&number of columns in the cell block

LB&lowest value (integer) used to prime the macro

UB&highest value (integer) used to prime the macro

decs&limit to number of decimal places in values created.

Hence, to produce the figures in the table illustrated above, this is the form of the macro to use:

dmwFillNumbers(5,8,3000,100000,2)

Input ?Call dmwFillNumbers(5,8,3000,100000,2) into the Immediate Window of Excel’s Visual Basic Editor to execute the macro.


The VBA Code

Here is the VBA code for the dmwFillNumbers macro:

Sub dmwFillNumbers(rows&, cols&, LB&, UB&, decs&)

On Error Goto errHandler

Dim n!, frmt$

Dim iRow&, iCol&

Dim rng As Range, rngFill As Range

Dim msg$, icon&, title$

If decs& > 0 Then

frmt$ = "0." & String(decs&, "0")

Else

frmt$ = "0"

End If

Set rng = ActiveCell

For iCol& = 0 To cols&

For iRow& = 0 To rows&

Set rngFill = rng.Offset(iRow&, iCol&)

With rngFill

n! = _

WorksheetFunction.RandBetween(LB&, LB&)

.Value = n! / (10 ^ decs&)

.NumberFormat = frmt$

End With

Next iRow&

Next iCol&

procDone:

Exit Sub

errHandler:

title$ = "Fill Colors Macro Error"

icon& = vbOKOnly + vbExclamation

msg$ = Err.Number & " " & Err.Description

MsgBox msg$, icon&, title$

Resume procDone

End Sub

Note that when you run this macro in your spreadsheet, Undo will not reverse the action. So, please, test the macro thoroughly before relying on it — I’d hate it to total any of your spreadsheets!


Interactive Random-Fill Macro

As an alternative to running the macro from the VBE’s Immediate Window, here is dmwRandbetween, which you can call in the usual way from Excel’s Macro dialog box:

Excel macro dialog box

If you’re going to use the macro a lot, then you may want to assign the keyboard shortcut Ctrl + t to the macro.

On running dmwRandbetween you will be presented with input boxes by which the macro collects the values you want input to the rows and columns:

Sub dmwRandbetween()

On Error Goto errHandler

Dim n!, frmt$

Dim rows&, cols&, LB&, UB&, decs&

Dim iRow&, iCol&

Dim rng As Range, rngFill As Range

Dim msg$, icon&, title$

title$ = "ROW FILL"

msg$ = "How many rows?"

rows& = InputBox (msg$, title$, 5)

title$ = "COLUMN FILL"

msg$ = "How many columns?"

cols& = InputBox (msg$, title$, 8)

title$ = "NUMBER"

msg$ = "Lowest number in fill?"

LB& = InputBox (msg$, title$, 3000)

msg$ = "Highest number in fill?"

UB& = InputBox (msg$, title$, 100000)

title$ = "PRECISION"

msg$ = "How many decimal places?"

decs& = InputBox (msg$, title$, 2)

If decs& > 0 Then

frmt$ = "0." & String(decs&, "0")

Else

frmt$ = "0"

End If

Set rng = ActiveCell

For iCol& = 0 To cols&

For iRow& = 0 To rows&

Set rngFill = rng.Offset(iRow&, iCol&)

With rngFill

n! = _

WorksheetFunction.RandBetween(LB&, UB&)

.Value = n! / (10 ^ decs&)

.NumberFormat = frmt$

End With

Next iRow&

Next iCol&

procDone:

Exit Sub

errHandler:

title$ = "MACRO ERROR"

icon& = vbOKOnly + vbExclamation

msg$ = Err.Number & " " & Err.Description

MsgBox msg$, icon&, title$

Resume procDone

End Sub

Note that when you run this macro in your spreadsheet, Undo will not reverse the action. DMW Consultancy Ltd will not accept responsibility for the outcome of any of your uses of the macros described on this page.


Your Support for dmw TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —

If you’d like an invoice to account for your donation, let me know how much you’re donating —

Invoice

Thanks, in anticipation.

SSL Cerification

“A good choice of number representation is essential for efficient calculation. Try, for example, to multiply XXVII by XXXIX!”

SH Hoolingdale and GC Tootill, Electronic Computers (1965)