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

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