Bonsoir,
Avec ce code dans un module.
Sub test()
Dim i%, aCount%, bCount%, cCount%, dCount%, eCount%, fCount%, j%, k As Byte
Dim Data As Variant
With Sheets(1)
ReDim Data(.Range("A1000").End(xlUp).Row - 4, 6)
aCount = 1
bCount = 1
cCount = 1
dCount = 1
eCount = 1
j = 1
For i = 5 To .Range("A1000").End(xlUp).Row - 1
If .Range("A" & i) <> "" Then
Data(j, 0) = .Range("A" & i)
aCount = 1
Else
Data(j, 0) = .Range("A" & i - aCount)
aCount = aCount + 1
End If
If .Range("B" & i) <> "" Then
Data(j, 1) = .Range("B" & i)
bCount = 1
Else
Data(j, 1) = .Range("B" & i - bCount)
bCount = bCount + 1
End If
If .Range("C" & i) <> "" Then
Data(j, 2) = .Range("C" & i)
cCount = 1
Else
Data(j, 2) = .Range("C" & i - cCount)
cCount = cCount + 1
End If
If .Range("D" & i) <> "" Then
Data(j, 3) = .Range("D" & i)
dCount = 1
Else
Data(j, 3) = .Range("D" & i - dCount)
dCount = dCount + 1
End If
If .Range("E" & i) <> "" Then
Data(j, 4) = .Range("E" & i)
eCount = 1
Else
Data(j, 4) = .Range("E" & i - eCount)
eCount = eCount + 1
End If
Data(j, 5) = .Range("F" & i)
j = j + 1
Next
For k = 0 To 5
Data(0, k) = Cells(4, k + 1)
Next k
Data(UBound(Data), 0) = "Total"
Data(UBound(Data), 5) = .Range("F1000").End(xlUp)
Sheets(2).Range("A4:F" & .Range("F1000").End(xlUp).Row) = Data
End With
End Sub
Ça pique un peu les yeux, les puristes m'en voudront mais j'ai un peu la flemme d'alléger...
Dites moi quand même si ça vous convient.
Cdlt,
Darzou