VBA Worksheet_Change

Dim nbLig As Long
Dim V
 
Private Sub Worksheet_Change(ByVal Target As Range)
  Application.EnableEvents = False
    If Target.CurrentRegion.Rows.Count = nbLig + 1 Then Exit Sub
    If V Then Target.Value = V
  Application.EnableEvents = True
End Sub
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    nbLig = Target.CurrentRegion.Rows.Count
    V = Target.Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim n1 As Double, lig1 As Long, n2 As Double, lig2 As Long
Application.EnableEvents = False
n1 = Application.CountA(Cells)
If n1 Then lig1 = Cells.Find(What:="*", After:=[A1], LookIn:=xlFormulas, _
  SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Application.Undo
n2 = Application.CountA(Cells)
If n2 Then lig2 = Cells.Find(What:="*", After:=[A1], LookIn:=xlFormulas, _
  SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
If n1 = n2 And lig1 = lig2 + 1 Then Application.Undo 'si insertion d'UNE ligne
Application.EnableEvents = True
End Sub