VBA : déclarer des plages multiples en une plage

    Dim nm As Name, tmp
    For Each nm In ThisWorkbook.Names
        If Left(LCase(nm.Name), 5) = "plage" Then
            tmp = Split(Mid(nm.RefersTo, 2), "!")
            Worksheets(tmp(0)).Range(tmp(1)).ClearContents
        End If
    Next nm

Sub EraseAll()
Dim WshName As Variant, RngAddress As Variant, i As Integer
    WshName = Array("Feuil2", "Feuil6", "Feuil4", "Feuil3", "Feuil5", "Feuil1")
    RngAddress = Array("A2:F18", "B6:X61, Z6:AL61", "B12:C68, e12:f68, h12:i68", "A1", "R190:PP191", "$C$2:$C$11,$E$7:$E$11,$G$11")
    For i = LBound(WshName) To UBound(WshName)
        With Worksheets(WshName(i))
            .Range(RngAddress(i)).ClearContents
        End With
    Next i
End Sub

Dim MesPlages As Variant, i As Integer
    MesPlages = Array("Feuil2!$B$5,$E$13,$C$5:$D$13", "Feuil1!$A$1", "Feuil3!B12:C68, e12:f68, h12:i68")
    For i = LBound(MesPlages) To UBound(MesPlages)
        Range(MesPlages(i)).ClearContents
    Next

Sub essai()

Dim MesPlages As Variant, i As Integer
    MesPlages = Array("Feuil2!$B$5,$E$13,$C$5:$D$13", "Feuil1!$A$1", "Feuil3!B12:C68, e12:f68, h12:i68")
    For i = LBound(MesPlages) To UBound(MesPlages)
        Range(MesPlages(i)).ClearContents
    Next
End Sub

Sub EraseAll()
Dim WshName As Variant, RngAddress As Variant, i As Integer
    WshName = Array("Feuil2", "Feuil6", "Feuil4", "Feuil3", "Feuil5", "Feuil1")
    RngAddress = Array("A2:F18", "B6:X61, Z6:AL61", "B12:C68, e12:f68, h12:i68", "A1", "R190:PP191", "$C$2:$C$11,$E$7:$E$11,$G$11")
    For i = LBound(WshName) To UBound(WshName)
        With Worksheets(WshName(i))
            .Range(RngAddress(i)).ClearContents
        End With
    Next i
End Sub

Sub essai()
Dim MesPlages As Variant, i As Integer
    MesPlages = Array("Feuil2!$B$5", "Feuil2!$E$13")
Mesplages2 = array("Feuil3!a1", "feuil3!a5")
    For i = LBound(MesPlages) To UBound(MesPlages)
        Range(MesPlages(i)).copy
    Next
   Range(mesplages2).paste
End Sub

Sub mail_frn()

Dim SHPa As Worksheet
Set SHPa = ThisWorkbook.Worksheets("Projet abonnement")
Dim SHLFRN As Worksheet
Set SHLFRN = ThisWorkbook.Worksheets("Liste FRN")
Dim FR1 As Worksheet
Set FR1 = ThisWorkbook.Worksheets("FRN1")
Dim FR2 As Worksheet
Set FR2 = ThisWorkbook.Worksheets("FRN2")

Dim Plagefrn1 As Range
Set Plagefrn1 = SHPa.Range("A4:q20")
Dim Plagefrn2 As Range
Set Plagefrn2 = SHPa.Range("a23:q46")

Application.ScreenUpdating = False

If SHPa.Cells(3, 3) = SHLFRN.Columns(1).Find(What:=SHPa.Cells(3, 3), LookAt:=xlPart) Then
Plagefrn1.Copy
            FR1.Activate
            Cells(1, 1).Select
            Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
            , SkipBlanks:=False, Transpose:=False
            Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
'Cells.Select
'Selection.Clear
End If


If SHPa.Cells(22, 3) = SHLFRN.Columns(1).Find(What:=SHPa.Cells(22, 3), LookAt:=xlPart) Then
Plagefrn2.Copy
            FR2.Activate
            Cells(1, 1).Select
            Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
            , SkipBlanks:=False, Transpose:=False
            Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
'Cells.Select
'Selection.Clear
End If

End Sub