Re : Concatener des cellules selon conditions (hors VBA !!!)
Bonsoir Regueiro, Pyramide91 , le forum.
A vouloir concatener dans une seule cellule, tu vas vite atteindre certaines limites :
Affichage du contenu de ta cellule,
Largeur des colonnes,
ou hauteur des lignes comme dans mon exemple.
Sinon vois ceci :
[highlight= vba]Option Explicit
Sub Essai()
Dim a, i As Long, j As Long, n As Long
Application.ScreenUpdating = False
With Sheets("Feuil1")
a = .Range("A1").CurrentRegion
End With
a(1, 1) = "Commandes": a(1, 2) = "Produits"
n = 1
For i = 2 To UBound(a, 1)
If a(i, 1) <> "" And Left(a(i, 1), 5) <> "Total" Then
n = n + 1
For j = 1 To UBound(a, 2)
a(n, j) = a(i, j)
Next
Else
If a(i, 2) <> "" Then
a(n, 2) = Join$(Array(a(n, 2), a(i, 2)), vbLf)
'a(n, 2) = Join$(Array(a(n, 2), a(i, 2)), "-")
End If
End If
Next
With Sheets("Feuil2").Range("a1").Resize(n, UBound(a, 2))
.CurrentRegion.Clear
.Value = a
.Font.Name = "calibri"
.Font.Size = 11
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Borders.Weight = 2
With .Rows(1)
.Interior.ColorIndex = 43
.Font.Size = 12
End With
.Offset(1).Resize(.Rows.Count - 1).VerticalAlignment = xlTop
.Columns.AutoFit
.Parent.Select
End With
Application.ScreenUpdating = True
End Sub
[/code]