Sub StuffingV2()


ActiveSheet.PageSetup.Orientation = xlLandscape



'supprimer doublon
Range("$A$3:$AA$10000").RemoveDuplicates Columns:=1, Header:=xlYes

'supprimer colonnes inutiles
Range("H:O,Q:AA").Select
    Selection.Delete Shift:=xlToLeft



Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete


    
'insérer une ligne
    Rows("2:2").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

'insérer une ligne
    Rows("2:2").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

'Unmerge et effacer A1 B1
    Range("A1:B1").Select
    Selection.UnMerge
    Selection.ClearContents

'hauteur de ligne 45
Rows("1:1").Select
Selection.RowHeight = 58

Range("A2").Value = "CLIENT :"
Range("B2").Value = "20' DRY"
Range("C2").Value = "20' FR"
Range("D2").Value = "40' DRY"
Range("E2").Value = "40' HC"
Range("F2").Value = "40' OT"
Range("G2").Value = "'40' FR :"
Range("H2").Value = "NUMBER :"
Range("I2").Value = "SEAL :"
Range("I4").Value = "COMMENTS :"


Columns("C").Replace What:="Customs Manifest for", Replacement:="STUFFING LIST N° "

'MISE EN FORME
Range("C1:G1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColor = 16777215
        .Color = 12611584
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("A2:I2").Select
    With Selection.Interior
        .PatternColor = 16777215
        .Color = 12611584
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
    Range("A4:I4").Select
    With Selection.Interior
        .PatternColor = 16777215
        .Color = 12611584
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

    
       Range("A2:Z1000").Select
        With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
       
    End With
    

Dim cellule As Range
    For Each cellule In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
        If cellule <> "" Then cellule.Resize(, 9).Borders.weight = xlThin
    Next
    
    
Range("A3:I3").Borders.LineStyle = 1

     Columns("B:G").ColumnWidth = 12
    Columns("A:A").ColumnWidth = 28
    Columns("H:I").ColumnWidth = 20
    
   
   Range("G5:G1000").Select
   Selection.ClearContents
    
'
    Range("F4:F1000").Select
    Selection.Copy
    Range("G4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
         
    
'Calcul volume
Dim line As Integer
line = 5
While Range("A" & line).Value <> "TOTAL:"
Cells(line, 6).FormulaR1C1 = "=RC[-3]*RC[-2]*RC[-1]/1000000"
line = line + 1
Wend


'Somme colonnes F & G et NbVal colonne B
[F65000].End(xlUp).Offset(1, 0).Select
ActiveCell = "=SUM(F5:F" & ActiveCell.Offset(-1, 0).Row & ")"

[G65000].End(xlUp).Offset(1, 0).Select
ActiveCell = "=SUM(G5:G" & ActiveCell.Offset(-1, 0).Row & ")"

[B65000].End(xlUp).Offset(1, 0).Select
ActiveCell = "=Counta(B5:B" & ActiveCell.Offset(-1, 0).Row & ")"

   
   'Format Cellules C à E, 0 chiffre après virgule
    Range("C5:E1000").Select
    Selection.NumberFormat = "#,##0"
 
 
 'Renvoyer à la ligne colonne H
    Columns("H:H").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With


Range("F4").Value = "Vol (m3)"

    Range("A2:I4").Select
    Selection.Font.Bold = True

  Cells.Select
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With


    Rows("1:1").Select
    With Selection
        .VerticalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
    End With
    With Selection.Font
        .Name = "Calibri"
        .Size = 16
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    


End Sub