Option Explicit
Option Base 1
Sub opt1()
Dim a() As Variant, b() As Variant, i As Long
'a() = RndIntPick(4, 27, 4, True) 'True=Sort a()
a() = RndIntPick(4, 27, 4)
'Debug.Print Join(a(), vbLf), vbLf
b() = Array("A8", "C8", "F8", "H8")
Worksheets("Sheet2").UsedRange.Clear
With Worksheets("Sheet1")
For i = 1 To 4
'Debug.Print .Range(.Cells(1, a(i)), .Cells(24, a(i))).Address
.Range(.Cells(1, a(i)), .Cells(24, a(i))).Copy Worksheets("Sheet2").Range(b(i))
Next i
End With
End Sub
Sub opt2()
Dim a() As Variant, i As Long
'a() = RndIntPick(4, 27, 4, true) 'True=Sort a()
a() = RndIntPick(4, 27, 4)
Worksheets("Sheet2").UsedRange.Clear
With Worksheets("Sheet1")
For i = 1 To 4
.Range(.Cells(1, a(i)), .Cells(24, a(i))).Copy Worksheets("Sheet2").Cells(8, i)
Next i
End With
End Sub
Function RndIntPick(first As Long, last As Long, _
noPick As Long, Optional bSort As Boolean = False) As Variant
Dim i As Long, r As Long, temp As Long, k As Long
ReDim iArr(first To last) As Variant
Dim a() As Variant
For i = first To last
iArr(i) = i
Next i
Randomize
For i = 1 To noPick
r = Int(Rnd() * (last - first + 1 - (i - 1))) + (first + (i - 1))
temp = iArr(r)
iArr(r) = iArr(first + i - 1)
iArr(first + i - 1) = temp
Next i
ReDim Preserve iArr(first To first + noPick - 1)
ReDim a(1 To noPick)
For r = 1 To noPick
a(r) = iArr(LBound(iArr) + r - 1)
Next r
If bSort = True Then
RndIntPick = ArrayListSort(a())
Else
RndIntPick = a()
End If
End Function
Function ArrayListSort(sn As Variant, Optional bAscending As Boolean = True)
With CreateObject("System.Collections.ArrayList")
Dim cl As Variant
For Each cl In sn
.Add cl
Next
.Sort 'Sort ascendending
If bAscending = False Then .Reverse 'Sort and then Reverse to sort descending
ArrayListSort = .toarray()
End With
End Function
Sub ken()
Dim t1 As Double, i As Integer, a(1 To 10000) As Variant
For i = 1 To 10000
a(i) = i
Next i
t1 = Timer '2.85 s
MsgBox Join(InsertSort(a()), vbLf), vbInformation, CStr(Timer - t1) & " seconds for InsertSort."
t1 = Timer '0.10 s
MsgBox Join(ArrayListSort(a()), vbLf), vbInformation, CStr(Timer - t1) & " seconds for ArrayList sort."
End Sub