Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre questionil faudrait peut-être trier les colonnes B et C afin de mettre les données sur la première colonne visible
Set bd = Sheets("BD") dl = bd.Cells(Application.Rows.Count, 1).End(xlUp).Row Set pl = bd.Range("B8:B" & dl)
Sub Stt() Dim bd As Object '(onglet BD) Dim dl As Integer '(Dernière Ligne) Dim pl As Range 'PLage) Dim dico As Object '(DICtiOnnaire) Dim cel As Range '(CELlule) Dim temp As Variant '(tableau TEMPoraire) Dim i As Integer '(Incrément) Dim o As Object '(Onglet) Dim dics As Object 'DICtionnaireS) Dim teo As Variant '(tableau TEmporaire Outils) Dim z As Integer 'variable z Dim lg As Integer Dim dercol As Integer '(Dernière colonne) Dim Obs As String '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Application.ScreenUpdating = False Set bd = Sheets("BD") dl = bd.Cells(Application.Rows.Count, 1).End(xlUp).Row Set pl = bd.Range("B2:B" & dl) 'Tri BD avant traitement Range("B2:M" & dl).Select bd.Sort.SortFields.Add Key:=Range( _ "B2:B" & dl), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal bd.Sort.SortFields.Add Key:=Range( _ "C2:C" & dl), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With bd.Sort .SetRange Range("B1:M71") .Apply End With ' Fin tri BD 'Début concaténation et suppression ligne pour "Stt" bd.Range("A1").AutoFilter Field:=2, Criteria1:="Stt" Set dico = CreateObject("Scripting.Dictionary") For Each cel In pl.Offset(0, 2).SpecialCells(xlCellTypeVisible) dico(cel.Value) = "" Next cel temp = dico.keys For i = 0 To UBound(temp) bd.Range("A1").AutoFilter Field:=4, Criteria1:=temp(i) If bd.Range("B:B").SpecialCells(xlCellTypeVisible).Areas(1).Count > 1 Then lg = 2 Else lg = bd.Range("B:B").SpecialCells(xlCellTypeVisible).Areas(2).Item(1).Row End If z = Application.Subtotal(3, [pl]) 'concatener If z > 1 And Cells(lg, 3) <> Cells(lg + 1) Then Obs "I " & Cells(lg + 1, 3) & " " & Cells(lg + 1, 10) & "mA " & " ; " & Cells(lg + 1, 13) & " ; " & Cells(lg, 13) Obs = Replace(Obs, Chr(10) & Chr(10), Chr(10)) Cells(lg, 13).Value = Obs Rows(lg + 1).Delete End If Next i bd.Range("A1").AutoFilter MsgBox "terminé!" End Sub
Pour moi concaténer c'est : "BON" et "JOUR" si tu concatènes, ca fait "BONJOUR"...