tirage aléatoire

Private Sub Worksheet_Activate()
Dim tablo, e, n&, source$(), ub&, c As Range, P As Range, i%
tablo = Sheets("Feuil1").[C1].CurrentRegion.Offset(1)
For Each e In tablo 'balayage d'abord par colonne puis par ligne
    If e <> "" Then
        n = n + 1
        ReDim Preserve source(1 To n)
        source(n) = e
    End If
Next e
ub = n
n = 0
Application.ScreenUpdating = False
For Each c In UsedRange
    If c = "N° Equipe" Then
        c(2).Resize(4, 2) = "" 'RAZ sur 2 colonnes
        Set P = Union(IIf(P Is Nothing, c(2), P), c(2))
    End If
Next c
For i = 1 To 4
    For Each c In P
        n = n + 1
        If n > ub Then Exit Sub
        c(i) = source(n)
Next c, i
End Sub

Private Sub Worksheet_Activate()
Dim tablo, n&, c As Range
tablo = Sheets("RANDOM EQUIPES").[C1].CurrentRegion.Resize(, 2)
Application.ScreenUpdating = False
On Error Resume Next
For Each c In UsedRange
    If c = "N° Equipe" Then
        c(2).Resize(4, 2) = "" 'RAZ sur 2 colonnes
        n = n + 2
        c(2) = tablo(n, 1)
        c(3) = tablo(n, 2)
        c(4) = tablo(n + 1, 1)
        c(5) = tablo(n + 1, 2)
    End If
Next c
End Sub

Private Sub Worksheet_Activate()
Dim tablo, e, n&, source$(), ub&, c As Range, P As Range
tablo = Sheets("RANDOM EQUIPES").[C1].CurrentRegion.Offset(1)
For Each e In tablo 'balayage d'abord par colonne puis par ligne
    If e <> "" Then
        n = n + 1
        ReDim Preserve source(1 To n)
        source(n) = e
    End If
Next e
ub = n
n = 0
Application.ScreenUpdating = False
For Each c In UsedRange
    If c = "N° Equipe" Then
        c(2).Resize(4, 2) = "" 'RAZ sur 2 colonnes
        Set P = Union(IIf(P Is Nothing, c(2), P), c(2))
    End If
Next c
For Each e In Array(1, 3, 2, 4) 'ordre des lignes
    For Each c In P
        n = n + 1
        If n > ub Then Exit Sub
        c(e) = source(n)
Next c, e
End Sub