Random number select

[lou-jj]

I need a macro that randomly selects a value in a given column. For example... I have a list of employee ids in column "A" from A1 thru A200... I need to run the macro and randomly select one of the values... and... if the cell value is blank it should not be selected... any help will be greatly appreciated.

Thanks

 

[jindon]

Hi
try

Sub test()
Dim rng As Range, r As Range, a, i As Long, n As Long, x As Range, myCount As Long
On Error GoTo Exit_Sub
Set rng = Application.InputBox("Select Range",type:=8)
n = Application.InputBox("Number of record(s) to select",type:=1)
On Error GoTo 0
n = Int(n)
If n <1 or rng.Cells.Count < n Then
      MsgBox "Invalid Number Entry"
      Exit Sub
ElseIf n = rng.Cells.Count Then
      rng.Select
      Exit Sub
End If
ReDim a(1 To rng.Cells.Count, 1 To 3)
Randomize
For Each r In rng
      i = i + 1
      a(i,1) = r.Value : a(i,2) = r.Address : a(i,3) = Rnd()
Next
VSortMA a, 1, i, 3
For i = 1 To UBound(a,1)
     If Not IsEmpty(a(i,1)) Then
          myCount = myCount + 1
          If x Is Nothing Then
               Set x = Range(a(i,2))
          Else
               Set x = Union(x, Range(a(i,2)))
          End If
      End If
      If myCount = n Then Exit For
Next
x.Select
Exit_Sub:
End Sub

Private Sub VSortMA(ary, LB, UB, ref)
Dim M As Variant, temp, i AS Long, ii As Long, iii As Long
i = UB : ii = LB
M = ary(Int((LB + UB) / 2), ref)
Do While ii <= i
      Do While ary(ii, ref) < M
           ii = ii + 1
      Loop
      Do While ary(i, ref) > M
           i = i -1
      Loop
      If ii <=i Then
           For iii = 1 To UBound(ary,2)
                temp = ary(ii,iii) : ary(ii,iii) = ary(i,iii) : ary(i,iii) = temp
           Next
           ii = ii + 1 : i = i - 1
      End If
Loop
If LB < i Then VSortMA ary, LB, i, ref
If ii < UB Then VSortMA ary, ii, UB, ref
End Sub