Certainly:
Sub SelectAllRandomly()
'first row with data to choose from
Const FirstRow = 1
'last row with data to choose from
Const LastRow = 100
'column the data to choose from is in
Const sourceColumn = "B"
'column to put random list into
Const newColumn = "D"
Dim destList As Range
Dim anyDestEntry As Range
Dim newFind As Variant
Dim lookAtRow As Long
Dim goFlag As Boolean
Dim foundFlag As Boolean
Dim movedCount As Long
Do While movedCount < (LastRow - FirstRow + 1)
lookAtRow = Int((LastRow - FirstRow + 1) * Rnd + FirstRow)
newFind = Range(sourceColumn & lookAtRow).Value
'for first time thru the loop
If Not goFlag Then
Range(newColumn & FirstRow) = newFind
movedCount = 1
goFlag = True
Else
Set destList = Range(newColumn & FirstRow & ":" _
& Range(newColumn & Rows.Count).End(xlUp).Address)
'is the item already in the list
foundFlag = False
For Each anyDestEntry In destList
If anyDestEntry = newFind Then
foundFlag = True ' already in list
Exit For
End If
Next
If Not foundFlag Then
Range(newColumn & destList.Rows.Count + 1) = newFind
movedCount = movedCount + 1
End If
End If
Loop
End Sub
Assumes that you'll build the new list on same sheet with the full set list.
To put the routine into your workbook:
First, work with a copy of the workbook just in case!
With the copy open, press [Alt]+[F11] to open the
VB Editor and choose
Insert -- Module and copy the code above and paste it into the module.
Change the values for:
Const FirstRow = 1
Const LastRow = 100
Const sourceColumn = "B"
Const newColumn = "D"
as needed for your worksheet setup. Save the workbook.
Run the macro.
"random sampling" wrote:
does any know if sampling from a set can be done until all values are selected?