How to generate large amounts of data for analysis using 3 ways in VBA. Watch the video below:

Often we need data for practice especially numerical data like sales, Wouldn’t it be helpful to generate such data automatically and quickly? We give below 3 macros to generate such data quickly and easily. Using arrays we can generate almost one million rows of data within 15 seconds. The other methods are relatively slow and require about 45 minutes each.

Sub FillRangeUsingArray() Dim rngCurrent As Range Dim lngRows As Long Dim lngCols As Long Dim lngR As Long Dim lngC As Long Dim varValues() As Variant Set rngCurrent = Range("B2:E999500") Dim startTime As Date, endTime As Date startTime = Timer lngRows = rngCurrent.Rows.Count lngCols = rngCurrent.Columns.Count ReDim varValues(0 To lngRows - 1, 0 To lngCols - 1) As Variant For lngR = 0 To lngRows - 1 For lngC = 0 To lngCols - 1 varValues(lngR, lngC) = Int((120000 - 60000 + 1) * Rnd + 30000) Next Next rngCurrent.Value = varValues() endTime = Timer MsgBox Format(endTime - startTime, "0.0") End Sub

Sub FillRange() Dim Col As Long Dim Row As Long Dim startTime As Date, endTime As Date startTime = Timer For Col = 2 To 5 For Row = 2 To 999500 ‘To create a random integer number between two values, we can use the following formula ‘Int ((upperbound – lowerbound + 1) * Rnd + lowerbound) Cells(Row, Col) = Int((120000 – 30000 + 1) * Rnd + 30000) Next Row Next Col endTime = Timer MsgBox Format(endTime – startTime, “0.0”) End Sub

Sub FillRange2() ‘Const MyRange = “$B$2:$E$999500” Dim cell As Range Dim startTime As Date, endTime As Date startTime = Timer For Each cell In Range(“B2:E999500”) cell = Int((120000 - 30000 + 1) * Rnd + 30000) Next cell endTime = Timer MsgBox Format(endTime – startTime, “0.0”) End Sub