Bonjour,
J'ai ajouté une suite à votre code pour finaliser la mise en forme. Cependant ça ne fonctionne pas, Excel plante.
Sub MiseEnforme()
'D?clarations variables
Dim Lig&, Ligg&, i&
'Derni?re ligne non vide en colonne B
Lig = Cells(Rows.Count, 2).End(xlUp).Row
'insertion colonne
Application.ScreenUpdating = False
Columns("A:A").Insert Shift:=xlToRight
'Insertion formule
With Range("A5:A" & Lig)
.FormulaR1C1 = "=IF(COUNTA(RC[1]:RC[3])=2,RC[1],"""")"
.Value = .Value ' ?quivaut ? Copier/Valeurs seules
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Value = .Value
End With
For i = Lig To 5 Step -1
If Cells(i, 2).Font.Bold Then
Cells(i, 2).EntireRow.Delete 'suppression ligne en gras
End If
Next
Ligg = Cells(Rows.Count, 1).End(xlUp).Row
Columns("D
").Insert Shift:=xlToRight
'concat?nation
[D5].NumberFormat = "General": [D5].Value = [D5].Value * 1
Range("D5
" & Ligg).Formula = "=A5&"" ""&B5&"" ""&C5"
'suppression lignes 1 et 2
Rows("1:2").Delete Shift:=xlUp
Rows("2:2").Select 's?lectionne la ligne 2
Selection.AutoFilter 'mettre un filtre
ActiveSheet.Range("$A$2:$AC$65000").AutoFilter Field:=1, Criteria1:=Array( _
"0", "Dossier", "PORT"), Operator:=xlFilterValues 'S?lectionne les cellules "Dossier" et "PORT"
Rows("3:3").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.SmallScroll Down:=3
Selection.Delete Shift:=xlUp 'supprime
ActiveSheet.Range("$A$2:$AC$65000").AutoFilter Field:=1 'enleve le filtre
Range("B2").Select 'filtre en colonne B2
ActiveSheet.Range("$A$2:$AC$65000").AutoFilter Field:=2, Criteria1:= _
"Etat GTIQ711a" 'Sélectionne les lignes qui contiennent "Etat GTIQ711a"
Rows("26:26").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.SmallScroll Down:=6
Selection.Delete Shift:=xlUp
ActiveSheet.Range("$A$2:$AC$65000").AutoFilter Field:=2 'supprime
ActiveWindow.SmallScroll Down:=-6
Columns("B:B").ColumnWidth = 7
Columns("C:C").ColumnWidth = 7
Columns("D").ColumnWidth = 20
Columns("E:E").ColumnWidth = 7
Columns("F:F").ColumnWidth = 3.22
Columns("G:G").ColumnWidth = 20
Columns("H:H").ColumnWidth = 0.88
Range("N2").Select 'S?lectionne en N2
ActiveSheet.Range("$A$2:$AG$1002").AutoFilter Field:=14, Criteria1:="<>"
Rows("26:26").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Range("N2").Select
ActiveSheet.Range("$A$2:$AG$65000").AutoFilter Field:=14
ActiveWindow.SmallScroll Down:=-3
Range("B2").Select 'en colonne B2 je fais un filtre pour supprimer les cellules "vides" => mais qui ne le sont pas vraiment, il y a des espaces
ActiveSheet.Range("$A$2:$AD$967").AutoFilter Field:=2, Criteria1:="="
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("B2").Select
ActiveSheet.Range("$A$2:$AD$65000").AutoFilter Field:=2
Range("C2").Select 'je supprime les "vides de la colonen C
ActiveSheet.Range("$A$2:$AD$65000").AutoFilter Field:=3, Criteria1:="="
Range("C33").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("C2").Select
ActiveSheet.Range("$A$2:$AD$65000").AutoFilter Field:=3
ActiveWindow.SmallScroll Down:=-9
Range("D3").Select 'je recalcule la formule concatener
Application.Calculation = xlAutomatic
End Sub