Sub essai()
Dim myAreas As Areas, myArea As Range, i As Long, n As Long
Application.ScreenUpdating = False
With ActiveSheet.UsedRange
For i = .Rows.Count To 1 Step -1
If WorksheetFunction.CountA(.Rows(i)) = 0 Then
.Rows(i).EntireRow.Delete
End If
Next i
.VerticalAlignment = xlCenter
n = .Columns.Count
End With
Columns(1).Insert
With Range("b1", Range("b" & Rows.Count).End(xlUp)).Offset(, -1)
.Formula = "=if(left(b1,13)=""Customer Item"",1,""a"")"
.Value = .Value
On Error Resume Next
Columns(1).SpecialCells(2, 1).EntireRow.Insert
On Error GoTo 0
End With
On Error Resume Next
Set myAreas = Columns(1).SpecialCells(2).Areas
On Error GoTo 0
If myAreas Is Nothing Then Exit Sub
For Each myArea In myAreas
'Mise en forme de chaque zone
With myArea.CurrentRegion
With .Cells(1).Offset(, 1)
.Resize(3).BorderAround ColorIndex:=1, Weight:=2
.Interior.ColorIndex = 19
.Offset(1).Interior.ColorIndex = 36
.Offset(2).Interior.ColorIndex = 44
End With
.Offset(, 1).Resize(, .Columns.Count - 1).Borders(xlInsideVertical).Weight = xlThin
.Offset(, 1).Resize(, .Columns.Count - 1).BorderAround ColorIndex:=3, Weight:=3
End With
Next
'On Error Resume Next
'Columns(1).SpecialCells(4).EntireRow.Delete
'On Error GoTo 0
Columns(2).Resize(, n).AutoFit
Columns(1).Delete
Set myAreas = Nothing
Application.ScreenUpdating = True
End Sub