Option Explicit
Sub RecupNom()
Dim i As Integer, x As Integer
Dim ColBase As New Collection
Dim TabPlage, TabPlage2, TabPlage3 As Variant, Container As Variant
Dim Item As Variant
With Sheets("Feuil1")
TabPlage = .Range("Plage1") //
TabPlage2 = .Range("Plage2") // on suppose 3 plages
TabPlage3 = .Range("Plage3") //
End With
On Error Resume Next
For i = 1 To UBound(TabPlage)
If Not IsEmpty(TabPlage(i, 1)) Then
ColBase.Add CStr(Trim(TabPlage(i, 1))) & "#" & CStr(Trim(TabPlage(i, 2))), CStr(Trim(TabPlage(i, 1))) & "#" & CStr(Trim(TabPlage(i, 2)))
End If
Next
On Error Resume Next
For i = 1 To UBound(TabPlage2)
If Not IsEmpty(TabPlage2(i, 1)) Then
ColBase.Add CStr(Trim(TabPlage2(i, 1))) & "#" & CStr(Trim(TabPlage2(i, 2))), CStr(Trim(TabPlage2(i, 1))) & "#" & CStr(Trim(TabPlage2(i, 2)))
End If
Next
On Error Resume Next
For i = 1 To UBound(TabPlage3)
If Not IsEmpty(TabPlage3(i, 1)) Then
ColBase.Add CStr(Trim(TabPlage3(i, 1))) & "#" & CStr(Trim(TabPlage3(i, 2))), CStr(Trim(TabPlage3(i, 1))) & "#" & CStr(Trim(TabPlage3(i, 2)))
End If
Next
On Error GoTo 0
For Each Item In ColBase
Container = Split(Item, "#")
x = x + 1
With Sheets("Feuil2")
.Range("A" & 5 + x) = Container(0)
.Range("B" & 5 + x) = Container(1)
End With
Next
End Sub