“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

Fill Cells with Random Numbers

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 would 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 and 2016. It's a long time since wanting to use the macro in earlier versions than those, so I've not tested the current construction of the macro in them.


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 would 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.