XL 2010 [RESOLU] validation sans lignes vides

Option Explicit

Sub TestValidations()
Dim derlig As Long, i As Long, x As Integer, plage(1 To 5) As String

    On Error Resume Next

    With Feuil1
        .Range("a2:e65536").Validation.Delete
        derlig = .Range("a" & Rows.Count).End(xlUp).Row + 1
        x = 1
        plage(1) = "=Feuil2!$A$2:$A$65536"
        plage(2) = "=Feuil2!$B$2:$B$65536"
        plage(3) = "=Feuil2!$C$2:$C$65536"
        plage(4) = "=Feuil2!$D$2:$D$65536"
        plage(5) = "=Feuil2!$E$2:$E$65536"

        For i = 2 To derlig
            x = x + 1
            With .Range("a" & x)
                .Validation.Add Type:=xlValidateList, Formula1:=plage(1)
            End With
            With .Range("b" & x)
                .Validation.Add Type:=xlValidateList, Formula1:=plage(2)
            End With
            With .Range("c" & x)
                .Validation.Add Type:=xlValidateList, Formula1:=plage(3)
            End With
            With .Range("d" & x)
                .Validation.Add Type:=xlValidateList, Formula1:=plage(4)
            End With
            With .Range("e" & x)
                .Validation.Add Type:=xlValidateList, Formula1:=plage(5)
            End With
        Next i
    End With
End Sub

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("a2:e65000")) Is Nothing Then

    If ActiveCell = "" Then
        Call TestValidations
    Else
        Exit Sub
    End If
End If
End Sub