May 23, 2021

Select Random Data Automatically

How to select random data automatically using index function in VBA. Watch the video below:

Select Random Data Automatically

Often we may need to select a few customer names from a large list of regular customers so that we can offer them a discount. The best way to do the selection is to use random automation to avoid any controversy. In a similar fashion a website may offer a badge to a few selected regular contributors to motivate everyone to participate.

Here’s the complete VBA code to select random data automatically using the Index worksheet function:

Sub SelectRandomData()
Dim MyRange As Range
Dim lastrow As Long
Dim lMyRandomRow As Long
Dim lRows As Long
Dim i As Long
lastrow = Application.WorksheetFunction.CountA(Range(“A:A”))
Set MyRange = Sheet1.Range(“A2:A” & lastrow)
lRows = MyRange.Rows.Count
Range(“C1”) = “Selected Customers”
Range(“C1”).Font.Bold = True
For i = 2 To 11
lMyRandomRow = Int(Rnd() * lRows) + 1 Cells(i, 3) = Application.WorksheetFunction.Index(MyRange, lMyRandomRow, 1)
Next i
End Sub
VBA code to select random data
Select Random Data Automatically
Select Random Data Automatically