Private Sub CommandButton1_Click()
Dim tablo, ub&, i&, x$, j&
Application.ScreenUpdating = False
On Error Resume Next 'si aucune SpecialCell
With UsedRange.Columns(1)
If CommandButton1.Caption = "RAZ" Then
.AutoFilter Field:=1, Criteria1:=vbRed, Operator:=xlFilterFontColor 'filtre couleur
With .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
.Value = ""
.Font.ColorIndex = xlAutomatic
End With
.AutoFilter 'ôte le filtre
Else
tablo = .Value 'matrice, plus rapide
ub = UBound(tablo)
For i = 2 To ub
If tablo(i, 1) = "" Then
x = ""
For j = i + 1 To ub
If tablo(j, 1) = "" Then Exit For
x = x & ", " & tablo(j, 1) 'concatène
Next j
tablo(i, 1) = Mid(x, 3)
End If
Next i
.SpecialCells(xlCellTypeBlanks).Font.Color = vbRed
.Value = tablo
.SpecialCells(xlCellTypeBlanks).Font.ColorIndex = xlAutomatic
End If
End With
CommandButton1.Caption = IIf(CommandButton1.Caption = "RAZ", "Concaténer", "RAZ")