Ready to get started with anonymous questions today? It's easy! Learn more.
Option Explicit Function RandomSelection(aRng As Range) Dim myTarg As Range, _ SrcList, Rslt(), _ i As Long, j As Long, k As Long Application.Volatile SrcList = aRng.Value Set myTarg = Application.Caller With myTarg If .Areas.Count > 1 Then RandomSelection = _ "Function can be used only in a single contiguous range" Exit Function '<<<<< End If If .Rows.Count > 1 And .Columns.Count > 1 Then RandomSelection = _ "Selected cells must be in a single row or column" Exit Function '<<<<< End If If .Cells.Count > aRng.Cells.Count Then RandomSelection = _ "Range specified as argument must contain more cells than output selection" Exit Function '<<<<< End If ReDim Rslt(1 To IIf(.Rows.Count > 1, .Rows.Count, .Columns.Count)) End With j = UBound(SrcList, 1) For i = LBound(Rslt) To UBound(Rslt) k = Int(Rnd() * (j - LBound(SrcList, 1) + 1)) + LBound(SrcList, 1) Rslt(i) = SrcList(k, 1) SrcList(k, 1) = SrcList(j, 1) j = j - 1 Next i If myTarg.Rows.Count > 1 Then RandomSelection = Application.WorksheetFunction.Transpose(Rslt) Else RandomSelection = Rslt End If End Function
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Join the community of 500,000 technology professionals and ask your questions.