Sub Bouton1_Clic()
Dim Derlg As Long
Application.ScreenUpdating = False
Derlg = Range("A1").CurrentRegion.Rows.Count
Range("A2:C" & Derlg).SpecialCells(xlCellTypeBlanks).Delete
Derlg = Range("A1").CurrentRegion.Rows.Count
Range("A2:A" & Derlg).Copy
Range("B2:C" & Derlg).PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Range("A" & Derlg + 1).Select
Application.ScreenUpdating = True
End Sub
Sub Bouton1_Clic()
Dim i&, j&, k&, p As Range, v()
For i = 1 To 3
Set p = Range(Cells(2, i), Cells(Rows.Count, i).End(xlUp)).Cells
If p.Rows.Count > 1 Then
v = p.Value
j = 1
For k = 2 To UBound(v)
If v(k, 1) <> "" Then
j = j + 1: v(j, 1) = v(k, 1)
End If
Next
p.ClearContents
p.Resize(j).Value = v
End If
Next
End Sub
[a1] = "": ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Delete xlUp
[a2].Resize([a:c].Find("*", , , , xlByRows, xlPrevious).Row, 3).SpecialCells(xlCellTypeBlanks).Delete xlUp
Sub test()
Dim i&, j&, k&, m&, p As Range, v()
'Plage de données (avec la ligne de titre) :
With Range(Cells(9, 1), Cells(50, 3)).Cells
'Vous pouvez aussi mettre :
' With [A9:C50].Cells
'ou tout autre plage qui vous convient.
'Si vous modifiez la plage de données, il n'y a
'(du moins je l'espère) rien à modifier dans
'la suite du code.
For i = 1 To .Columns.Count
If .Columns(i).Cells(1) = "" Then .Columns(i).Cells(1) = " "
Set p = .Columns(i).Resize(.Cells(.Rows.Count + 1, i).End(xlUp).Row - .Row + 1)
If p.Rows.Count > 1 Then
v = p.Value
j = 1
For k = 2 To UBound(v)
If v(k, 1) <> "" Then
j = j + 1: v(j, 1) = v(k, 1)
End If
Next
p.ClearContents
p.Resize(j).Value = v
If j > m Then m = j
End If
Next
If m Then .Offset(m).Resize(.Rows.Count - m).Clear
End With
End Sub
If m Then .Offset(m).Resize(.Rows.Count - m).Clear
If m Then .Resize(.Rows.Count - m).Offset(m).Clear
Sub es()
Dim t(), x As Long, i As Long, j As Byte, z As Long
z = Columns(1).Find(What:="Remarques :", LookIn:=xlValues).Row - 10
For j = 1 To 3
x = 0
t = Cells(10, j).Resize(z, 1).Value
For i = 1 To UBound(t)
If t(i, 1) <> "" Then x = x + 1: t(x, 1) = t(i, 1)
Next i
Cells(10, j).Resize(z, 1) = ""
If x <> 0 Then Cells(10, j).Resize(x, 1) = t
Next j
End Sub
je sais pas si manipuler un filtre cela serait pas plus simple ???