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
| Sub SubligneFetGvide()
Dim oSh As Excel.Worksheet
Dim v As Variant, i As Long, Index As Long, j As Integer
Dim DerLig As Long, DerCol As Integer
Set oSh = ActiveSheet
DerLig = oSh.UsedRange.Rows(oSh.UsedRange.Rows.Count).Row
DerCol = oSh.UsedRange.Columns(oSh.UsedRange.Columns.Count).Column
If DerCol < 7 Then DerCol = 7 'au cas où toute la colonne G est vide ainsi que les colonnes suivantes
v = oSh.Range(oSh.Cells(1, 1), oSh.Cells(DerLig, DerCol)).Value
'tasser les lignes à conserver
Index = 1
For i = 1 To DerLig
If (v(i, 6) <> 0) Or (v(i, 7) <> 0) Then
For j = 1 To DerCol
v(Index, j) = v(i, j)
Next j
Index = Index + 1
End If
Next i
'vider les lignes restantes
For i = Index To DerLig
For j = 1 To DerCol
v(i, j) = Empty
Next j
Next i
oSh.Range(oSh.Cells(1, 1), oSh.Cells(DerLig, DerCol)).Value = v
Set oSh = Nothing
End Sub |
Partager