Sub SupprLignesVides(xplage As Range, Optional EnValeur)
' supprimer les lignes vides d'une plage
' si EnValeur est présent, on transforme le tableau en valeur
' ==> les formules sont alors perdues
Dim maplage, T, i&, j&, n&, s, Tplus
If xplage Is Nothing Then Exit Sub
Application.ScreenUpdating = False
With xplage.Parent
Set maplage = Intersect(.UsedRange, xplage.Areas(1))
If maplage Is Nothing Then Exit Sub
If maplage.Rows.Count = 1 Then Exit Sub
T = maplage.Value
If Not IsArray(T) Then
ReDim T(1 To 1, 1 To 1): T(1, 1) = maplage.Value
End If
If Not (IsMissing(EnValeur)) Then maplage.Value = T
ReDim Tplus(1 To UBound(T), 1 To 1)
For i = 1 To UBound(T)
s = ""
For j = 1 To UBound(T, 2): s = s & T(i, j): Next j
If s <> "" Then
Tplus(i, 1) = 1
n = n + 1
End If
Next i
If n = maplage.Rows.Count Then Exit Sub
Application.ScreenUpdating = False
maplage.Resize(, 1).Offset(, maplage.Columns.Count).Insert Shift:=xlToRight
maplage.Resize(, 1).Offset(, maplage.Columns.Count) = Tplus
maplage.Resize(, maplage.Columns.Count + 1).Sort _
key1:=maplage(1, maplage.Columns.Count + 1), order1:=xlAscending, _
Header:=xlNo
maplage.Columns(maplage.Columns.Count + 1).Delete Shift:=xlToLeft
maplage.Resize.Offset(n).Resize(maplage.Rows.Count - n).Clear
End With
End Sub