Récupérer les coordonnées de la dernière cellule

Public Sub AjoutListe(Entree As String, NomFeuille As String, Liste As Range)

' Macro enregistrée le 18/06/2007 par Chloulou et modifiée le 1/08/2007 par chloulou

'Objectif : ajouter une entree non presente dans les listes de remplissage des combobox

    Dim i As Integer
    Dim NbLigneListe As Long
    Dim ColonneListe As Long
    Dim Insertion As Boolean
    Dim MaPlage As Range
    Dim NomPlage As String
    Dim EntreeI As String
    Worksheets(NomFeuille).Activate
    
    NomPlage = Liste.Name.Name
    
    Insertion = False
    
    'récupérer le nombre de colonnes et de ligne de la liste nommée, sachant qu'elle est en colonne et commenance ligne 2
    NbLigneListe = Liste.Rows.Count
    ColonneListe = Liste.Column
    
    If Entree = "" Then
    
    Else
    
        For i = 1 To NbLigneListe
            
            EntreeI = Worksheets(NomFeuille).Cells(i, ColonneListe).Value
            
            If Entree <> EntreeI Then
                
                Insertion = True
                
            ElseIf Entree = EntreeI Then
                
                Insertion = False
                Exit For
                
            End If

         Next i

           Worksheets(NomFeuille).Activate
'
'            On Error Resume Next
'
'            Insertion = Application.WorksheetFunction.Match(Entree, Liste, 0)
'
'            On Error GoTo 0
            
            If Insertion = True Then
            
                
                Cells(NbLigneListe + 2, ColonneListe).Value = Entree
           
                Set MaPlage = Range(Cells(2, ColonneListe), Cells(NbLigneListe + 1, ColonneListe))
                MaPlage.Name = NomPlage
                MaPlage.Resize(MaPlage.Rows.Count + 1).Name = NomPlage
                
            End If
            
    End If
    
        
    End Sub

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim OldRange As Range, NewRange As Range, LastCel As Range
Dim Col As Integer

    On Error Resume Next
    Set OldRange = Range("Format")      'repère la plage nommée format
    If Err.Number <> 0 Then Exit Sub    ' sort de la macro si elle n'existe pas
    On Error GoTo 0                     ' réactivation de la gestion d'erreur

    Col = OldRange.Column
    
    If Target.Column = Col Then                     ' si une modification a lieu dans la même colonne que "format"
        Set LastCel = Cells(65535, Col).End(xlUp)   ' cherche la première cellule non vide à partir du bas de la colonne
        Set NewRange = Range(OldRange.Cells(1), LastCel) 'redéfinit la nouvelle plage comprise entre sa première cellule et la cell trouvée ci dessus
        NewRange.Name = "Format"                           ' renomme la plage
        NewRange.Select ' juste pour l'exemple, mais à supprimer
    End If

End Sub