VBA Function: Populating a Range
The following code will populate a given range with a random number between 0 and 1000:
Sub Randomise_Range(Cell_Range As Range) ' Will randomise each cell in Range Dim Cell 'Turn off screen alerts Application.ScreenUpdating = False For Each Cell In Cell_Range Cell.Value = Rnd * 1000 Next Cell Application.ScreenUpdating = True End Sub
The code “Application.ScreenUpdating =FALSE” and “Application.ScreenUpdating = TRUE” serve to turn off and turn on screen alerts – making the code much faster to run.
It can be set up via a click event, with the main routine specifying the range:
Private Sub CommandButton1_Click()
Randomise_Range (Sheets("Sheet3").Range("A1:T8000"))
End Sub
So this case, cells A1:T8000 on sheet 3 are populated with random numbers – once the command button 1 is clicked.
Can't get the tutorial to work for you? Need help with your code?
Get answers right away at our AE Excel Support Forums!
Get answers right away at our AE Excel Support Forums!
This is around 10 times faster. Define an array the size of Cell_Range, populate the array with random numbers, and populate Cell_Range with the array. By only writing to the worksheet once for the whole routine, instead of once per cell, yoou can save lots of time.
Sub Randomise_Range2(Cell_Range As Range)
‘ Will randomise each cell in Range
Dim v
Dim i As Long
Dim j As Long
‘Turn off screen alerts
Application.ScreenUpdating = False
ReDim v(1 To Cell_Range.Rows.Count, 1 To Cell_Range.Columns.Count)
For i = 1 To Cell_Range.Rows.Count
For j = 1 To Cell_Range.Columns.Count
v(i, j) = Rnd * 1000
Next j
Next i
Cell_Range.Value = v
Application.ScreenUpdating = True
End Sub
Your code took 0.46 to 0.68 seconds to populate A1:A5000 (in five trials), my code took 0.031 to 0.078 seconds.
Excel throws a “Application-defined or object-defined error” when the length of the string in any of the array’s element is > 911 characters.