Selection jusqu'a la dernière colonne non vide

Private Sub CommandButton1_Click()
'Programme par Jeremy Tirilly le 23/04/14
Dim derlig As Integer, style As Integer
Dim Msg As String, Title As String, Response As String

Application.ScreenUpdating = False
Msg = "Voulez-vous effacer tous les enregistrements du tableau ?"
style = vbYesNo + vbCritical + vbDefaultButton2
Title = "Séquence de Suppression"
Response = MsgBox(Msg, style, Title)
If Response = vbNo Then Exit Sub

Columns("AZ:BC").Select
    Selection.Copy

Range("B2:BC14").ClearContents
Range("B16:BC20").ClearContents
Range("B22:BC24").ClearContents
Range("B26:BC50").ClearContents
Range("B2:BC14").Interior.ColorIndex = xlNone
Range("B16:BC20").Interior.ColorIndex = xlNone
Range("B22:BC24").Interior.ColorIndex = xlNone
Range("B26:BC50").Interior.ColorIndex = xlNone

Range("B1").Select
    ActiveSheet.Paste

Application.Dialogs(xlDialogSaveAs).Show


End Sub

Sub TEST1()
    Range("AZ1").Select
    Range(selection, selection.End(xlToRight)).Select
    Range(selection, selection.End(xlDown)).Select
End Sub

Sub Test()

    Dim Plage As Range
    
    With Worksheets("Feuil1")
    
        Set Plage = .Range(.Cells(1, 52), _
                    .Cells(.Cells.Find("*", .[A1], -4123, , _
                    1, 2).Row, .Cells.Find("*", .[A1], -4123, , _
                    2, 2).Column))
    
    End With
    
    'pour le test
    MsgBox Plage.Address(0, 0)
    
    'ici ton code...
    
End Sub

Sub Test()

    Dim Plage As Range
    
    If MsgBox("Voulez-vous effacer tous les enregistrements du tableau ?", _
              vbYesNo + vbQuestion, _
              "Séquence de Suppression") = vbNo Then Exit Sub
    
    With Worksheets("Feuil1")
    
        Set Plage = .Range(.Cells(1, 52), _
                    .Cells(.Cells.Find("*", .[A1], -4123, , _
                    1, 2).Row, .Cells.Find("*", .[A1], -4123, , _
                    2, 2).Column))
    
    End With
    
    With Worksheets("Feuil2")
    
        .Range(.Cells(1, 2), .Cells(Plage.Rows.Count, Plage.Columns.Count + 1)).Value = Plage.Value
    
    End With
    
    Plage.ClearContents
    Plage.Interior.ColorIndex = xlNone
    
    Application.Dialogs(xlDialogSaveAs).Show
    
End Sub