1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36
| Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Nb As Integer, m As Integer
Dim c As Range
Application.ScreenUpdating = False
If Target.Count = 1 Then
If Target.Column = 6 Then
If Target.Offset(0, -1) <> "" Then
If Target.Offset(1, -1) <> "" Then
Set c = Target.Offset(1, -1)
Else
Set c = Target.Offset(1, -1).Resize(1000, 1).Find("*")
End If
If Not c Is Nothing Then
If c.Row > Target.Row Then
Nb = Val(Target.Value)
m = Nb - c.Row + Target.Row
If m <> 0 Then
Application.EnableEvents = False
If m > 0 Then
c.Offset(0, -1).Resize(m, 6).Insert shift:=xlDown
Target.Offset(0, 1).Copy c.Offset(-m, 2).Resize(m, 1)
Else
Target.Offset(Nb, -1).Resize(-m, 6).Delete shift:=xlUp
End If
Application.EnableEvents = True
End If
End If
End If
End If
End If
End If
End Sub |
Partager