Randomly pick string element from array

[Retroshift]

Hi, I have the following code which creates an array "Ary" with string elements from a table column that match value "x" from another column in this table.
I would like to have cell E6 display a randomly picked string element from this array "Ary". How would I have to alter the VBA code?

Sub test()
   Dim D3 As Range
   Dim Ary As Variant
   Dim i As Long
   
   With Sheets("Master").ListObjects("table1")
      ReDim Ary(1 To .ListRows.Count, 1 To 1)
      .Range.AutoFilter .ListColumns("Acol").Index, "x"
      For Each D3 In .ListColumns("Bcol").DataBodyRange.SpecialCells(xlVisible)
         i = i + 1
         Ary(i, 1) = D3.Value
      Next
    
   .AutoFilter.ShowAllData
   End With
End Sub

 

[Trebor76]

Hi Retroshift,

Add this line...

Range("E6").Value = Ary(WorksheetFunction.RandBetween(1, i), 1)

...immediatley below this line:

End With

Regards,

Robert

 

[Retroshift]

Thanks Trebor!

 

[Trebor76]

Thanks Trebor!

You're welcome