Array à partir d'une union de range

Sub Macro1()
Dim monTab As Variant
Dim plage1 As Range
Dim plage2 As Range

Set plage1 = Range("A1:A2")
Set plage2 = Range("C1:C2")

Application.ScreenUpdating = True
With Workbooks.Add.Sheets(1) 'document auxiliaire
  .[A1].Resize(plage1.Rows.Count, plage1.Columns.Count) = plage1.Value
  .[A1].Offset(, plage1.Columns.Count).Resize(plage2.Rows.Count, plage2.Columns.Count) = plage2.Value
  monTab = .UsedRange.Value
  .Parent.Close False
End With

MsgBox monTab(1, 1) 'Ca marche!
MsgBox monTab(1, 2) 'Ca marche!!

End Sub

Option Explicit
Sub test()
Dim monTab As Variant
Dim plage1 As Range
Dim plage2 As Range
Set plage1 = Range("A1:A2")
Set plage2 = Range("C1:C2")
With Application
    monTab = .Transpose((.Transpose(Array(.Transpose(plage1.Value), .Transpose(plage2.Value)))))
End With
MsgBox monTab(2, 2)
End Sub

.Transpose(.Transpose(Array.

.Transpose(plage1.Value)

Array(.Transpose(plage1.Value), .Transpose(plage2.Value))

With Application
    monTab = Array(.Transpose(plage1.Value), .Transpose(plage2.Value))
    monTab = .Transpose(monTab)
    monTab = .Transpose(monTab)
End With

Sub testPierrot()
Dim monTab As Variant
Dim montab2 As Variant
Dim x1 As Integer, x2 As Integer
Dim plage1 As Range
Dim plage2 As Range
Dim plage3 As Range
Dim i As Integer
t = Timer
For i = 1 To 100
Set plage1 = Range("A1:A2")
Set plage2 = Range("C1:C2")
Set plage3 = Range("E1:E2")
With Application
    monTab = Array(.Transpose(plage1.Value), .Transpose(plage2.Value), .Transpose(plage3.Value))
    monTab = .Transpose(monTab)
    monTab = .Transpose(monTab)
End With
ReDim montab2(UBound(monTab, 2), UBound(monTab, 1))
For x1 = 1 To UBound(monTab, 2)
For x2 = 1 To UBound(monTab, 1)
    montab2(x1, x2) = monTab(x2, x1)
Next x2
Next x1
Next i
MsgBox Timer - t
MsgBox montab2(2, 2)
MsgBox montab2(2, 3)
End Sub

Sub testjob()
Dim monTab As Variant
Dim plage1 As Range
Dim plage2 As Range
Dim plage3 As Range
Dim i As Integer
t = Timer
For i = 1 To 100
Set plage1 = Range("A1:A2")
Set plage2 = Range("C1:C2")
Set plage3 = Range("E1:E2")

Application.ScreenUpdating = True
With Workbooks.Add.Sheets(1) 'document auxiliaire
  .[A1].Resize(plage1.Rows.Count, plage1.Columns.Count) = plage1.Value
  .[A1].Offset(, plage1.Columns.Count).Resize(plage2.Rows.Count, plage2.Columns.Count) = plage2.Value
  .[A1].Offset(, plage1.Columns.Count + plage2.Columns.Count).Resize(plage3.Rows.Count, plage3.Columns.Count) = plage3.Value
  monTab = .UsedRange.Value
  .Parent.Close False
End With
Next i
MsgBox Timer - t
MsgBox monTab(1, 1) 'Ca marche!
MsgBox monTab(2, 3) 'Ca marche!!

End Sub

Sub essai1()
 Set plage1 = Range("A1:A2")
 Set plage2 = Range("C1:C2")
 For Each c In Union(plage1, plage2)
   MsgBox c
 Next c
End Sub

Sub essai3()
 a = Range("A1:A2")
 b = Range("C1:C2")
 c = Fusion(a, b)
 [e1].Resize(UBound(c)) = c
End Sub

Sub essai4()
 c = Fusion(Range("A1:A2"), Range("c1:c2"))
 [g1].Resize(UBound(c)) = c
End Sub

  a = [tab1].Value
  b = [tab2].Value
  c = [tab3].Value
  d = [tab4].Value
  e = Fusion(Fusion(a, b,c), d)

Sub Macro1()
Dim plage As Range, h&, monTab(), j%, a As Range, i&

Set plage = [A1:A2,C1:C2,E1:E2,G1:G2]
h = plage.Areas(1).Count

ReDim monTab(1 To h, 1 To plage.Areas.Count)

For j = 1 To plage.Areas.Count
  Set a = plage.Areas(j)
  For i = 1 To h
    monTab(i, j) = a(i)
  Next
Next
  
MsgBox monTab(1, 1)
MsgBox monTab(2, 4)

End Sub

Sub Macro1()
Dim t#, plage As Range, h&, monTab(), j%, a, i&

t = Timer
Set plage = [A1:A1000000,C1:C1000000,E1:E1000000,G1:G1000000]
h = plage.Areas(1).Count

ReDim monTab(1 To h, 1 To plage.Areas.Count)

For j = 1 To plage.Areas.Count
  a = plage.Areas(j)
  For i = 1 To h
    monTab(i, j) = a(i, 1)
  Next
Next

MsgBox Timer - t

End Sub

Sub Essai()
  ncol = 10
  h = 2
  Dim montab(): ReDim montab(h, ncol)
  a = [A1].Resize(h, ncol * 2)         ' lecture dans un tableau a()
  For c = 1 To ncol                        
    For i = 1 To h
       montab(i, c) = a(i, c * 2 - 1)    ' on prend 1 colonne sur 2
    Next i
  Next c
  MsgBox montab(1, 1)
  MsgBox montab(2, 3)
End Sub

Sub UnionDynamique()
  Set plage = Range("A1:A4")
  ncol = 10
  h = plage.Count
  intervalle = 2
  For col = 1 + intervalle To ncol * intervalle Step intervalle
    Set plage = Union(plage, Cells(1, col).Resize(h))
  Next col
  MsgBox plage.Address
End Sub

Private Sub RemplirTableauFLVL()
Dim MonTab()
Dim j As Integer, i As Integer, k As Integer, n As Integer, y As Integer, Lastline as Integer
Set Plage = Union(Plage1, Plage2, Plage3, Plage4, Plage5, Plage6, Plage7)
LastLine = Plage.Areas(1).Count
ReDim TableauFLVL(1 To LastLine, 1 To 7)
y = 1
For j = 1 To Plage.Areas.Count
    n = 1
    MonTab = Plage.Areas(j)
    MsgBox ("Pour la plage numéro " & j & "    la limite est " & UBound(MonTab, 2))
    For k = y To UBound(MonTab, 2) + y - 1
    For i = 1 To LastLine
    TableauFLVL(i, k) = MonTab(i, n)
    Next i
    n = n + 1
    Next k
    If UBound(MonTab, 2) > 1 Then y = y + 1
    y = y + 1
Next j
End Sub

Set plage = [A1:A2,B1:B2,D1:D2]