Option Explicit
Sub Random_Number2()
Dim NB_Nb_GRP1 As Integer
Dim NB_Nb_GRP2 As Integer
Dim NB_Nb_GRP3 As Integer
Dim NB_Nb_GRP4 As Integer
Dim I As Integer
Dim J As Integer
Dim K As Integer
Dim MyRANGE As Range
Dim F As Object
Dim IPRES
Dim TMP
Dim GRP1_Select2 As Integer
Dim GRP2_Select2 As Integer
Dim GRP3_Select2 As Integer
Dim GRP4_Select2 As Integer
GRP1_Select2 = Range("GRP1_Select2")
GRP2_Select2 = Range("GRP2_Select2")
GRP3_Select2 = Range("GRP3_Select2")
GRP4_Select2 = Range("GRP4_Select2")
'------ CLEANING PREVIOUS RESULT ------
Range("E1:E20").ClearContents
Range("F1:F20").ClearContents
'------ PREPARE FIRST AREA -------
J = 1
NB_Nb_GRP1 = WorksheetFunction.CountA(Range("Grp1_Select1"))
For I = 1 To NB_Nb_GRP1
Cells(J, "E") = Range("Grp1_Select1").Cells(I, 1)
J = J + 1
Next I
NB_Nb_GRP2 = WorksheetFunction.CountA(Range("Grp2_Select1"))
For I = 1 To NB_Nb_GRP2
Cells(J, "E") = Range("Grp2_Select1").Cells(I, 1)
J = J + 1
Next I
NB_Nb_GRP3 = WorksheetFunction.CountA(Range("Grp3_Select1"))
For I = 1 To NB_Nb_GRP3
Cells(J, "E") = Range("Grp3_Select1").Cells(I, 1)
J = J + 1
Next I
NB_Nb_GRP4 = WorksheetFunction.CountA(Range("Grp4_Select1"))
For I = 1 To NB_Nb_GRP4
Cells(J, "E") = Range("Grp4_Select1").Cells(I, 1)
J = J + 1
Next I
K = 1
Set MyRANGE = Range("E1:E20")
'================= FOR GROUP 1 =================
'------ PREPARE RANDOM AREA with GROUP -------
J = 1
With MyRANGE
Range("Working_Range").Offset(, 1).ClearContents '---- CLEAN UP PREVIOUS DATA
For Each F In Range("GRP1_Data")
Set IPRES = .Find(F, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)
If (IPRES Is Nothing) Then
Range("Working_Range").Cells(J, 2) = F.Value
J = J + 1
End If
Next F
End With
'------- RANDOMIZE ORDER GROUPE -------
With Range("Working_Range")
With Range(.Cells(1, 1), .Cells(.Rows.Count - NB_Nb_GRP1, 2))
.Sort Key1:=.Offset(0, 0), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
'------- COPY DATA IN SECOND AREA -------
For I = 1 To GRP1_Select2
Cells(K, "F") = .Cells(I, 2)
K = K + 1
Next I
End With
'================= FOR GROUP 2 =================
'------ PREPARE RANDOM AREA with GROUP -------
J = 1
With MyRANGE
Range("Working_Range").Offset(, 1).ClearContents '---- CLEAN UP PREVIOUS DATA
For Each F In Range("GRP2_Data")
Set IPRES = .Find(F, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)
If (IPRES Is Nothing) Then
Range("Working_Range").Cells(J, 2) = F.Value
J = J + 1
End If
Next F
End With
'------- RANDOMIZE ORDER GROUPE -------
With Range("Working_Range")
With Range(.Cells(1, 1), .Cells(.Rows.Count - NB_Nb_GRP2, 2))
.Sort Key1:=.Offset(0, 0), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
'------- COPY DATA IN SECOND AREA -------
For I = 1 To GRP2_Select2
Cells(K, "F") = .Cells(I, 2)
K = K + 1
Next I
End With
'================= FOR GROUP 3 =================
'------ PREPARE RANDOM AREA with GROUP -------
J = 1
With MyRANGE
Range("Working_Range").Offset(, 1).ClearContents '---- CLEAN UP PREVIOUS DATA
For Each F In Range("GRP3_Data")
Set IPRES = .Find(F, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)
If (IPRES Is Nothing) Then
Range("Working_Range").Cells(J, 2) = F.Value
J = J + 1
End If
Next F
End With
'------- RANDOMIZE ORDER GROUPE -------
With Range("Working_Range")
With Range(.Cells(1, 1), .Cells(.Rows.Count - NB_Nb_GRP3, 2))
.Sort Key1:=.Offset(0, 0), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
'------- COPY DATA IN SECOND AREA -------
For I = 1 To GRP3_Select2
Cells(K, "F") = .Cells(I, 2)
K = K + 1
Next I
End With
'================= FOR GROUP 4 =================
'------ PREPARE RANDOM AREA with GROUP -------
J = 1
With MyRANGE
Range("Working_Range").Offset(, 1).ClearContents '---- CLEAN UP PREVIOUS DATA
For Each F In Range("GRP4_Data")
Set IPRES = .Find(F, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)
If (IPRES Is Nothing) Then
Range("Working_Range").Cells(J, 2) = F.Value
J = J + 1
End If
Next F
End With
'------- RANDOMIZE ORDER GROUPE -------
With Range("Working_Range")
With Range(.Cells(1, 1), .Cells(.Rows.Count - NB_Nb_GRP4, 2))
.Sort Key1:=.Offset(0, 0), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
'------- COPY DATA IN SECOND AREA -------
For I = 1 To GRP4_Select2
Cells(K, "F") = .Cells(I, 2)
K = K + 1
Next I
End With
End Sub