1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65
| Sub formatage()
Dim nbre_ligne_data, DernLigne, cpt_ligne_data As Long
Dim test As Boolean
Dim DernColonne, nbre_ligne_pf, j As Integer
Dim item_pf, item_data As String
Application.ScreenUpdating = False
nbre_ligne_data = Workbooks("PICPDP.xlsm").Sheets("DATA").Cells(Rows.Count, 1).End(xlUp).Row
nbre_ligne_pf = Workbooks("PICPDP.xlsm").Sheets("LISTE PF").Cells(Rows.Count, 1).End(xlUp).Row
For cpt_ligne_data = 2 To nbre_ligne_data
test = False
For j = 2 To nbre_ligne_pf
item_pf = Workbooks("PICPDP.xlsm").Sheets("LISTE PF").Cells(j, 1).Value
item_data = Workbooks("PICPDP.xlsm").Sheets("DATA").Cells(cpt_ligne_data, 5).Value
If item_data = item_pf Then
test = True
Exit For
End If
Next
If test = False Then
'ici, traitement pour le cas où la valeur n'est pas trouvée
Workbooks("PICPDP.xlsm").Sheets("DATA").Rows(cpt_ligne_data).ClearContents
End If
Application.StatusBar = cpt_ligne_data
Next
DernLigne = Range("A" & Rows.Count).End(xlUp).Row
'dernière colonne ligne 1
DernColonne = Cells(1, Cells.Columns.Count).End(xlToLeft).Column
Cells.Select
ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Add Key:=Range("A1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("DATA").Sort
.SetRange Range(Cells(2, 1), Cells(DernLigne, DernColonne))
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Workbooks("PICPDP.xlsm").Sheets("DATA").Columns(23).Delete
Workbooks("PICPDP.xlsm").Sheets("DATA").Columns(22).Delete
Workbooks("PICPDP.xlsm").Sheets("DATA").Columns(17).Delete
Workbooks("PICPDP.xlsm").Sheets("DATA").Columns(13).Delete
Workbooks("PICPDP.xlsm").Sheets("DATA").Columns(11).Delete
Workbooks("PICPDP.xlsm").Sheets("DATA").Columns(4).Delete
Workbooks("PICPDP.xlsm").Sheets("DATA").Columns(3).Delete
Workbooks("PICPDP.xlsm").Sheets("DATA").Columns(2).Delete
Workbooks("PICPDP.xlsm").Sheets("DATA").Columns(1).Delete
End Sub |
Partager