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 66 67
| Option Explicit
'/!\ Active la Référence Microsoft Scripting Runtime
Sub Traiter()
Dim Wbk As Workbook
Dim Fichier
Fichier = Application.GetOpenFilename("Excel Files (*.xlsm*), *.xlsm*")
If Fichier <> False Then
Set Wbk = Workbooks.Open(Fichier)
Recap Wbk.Worksheets(2).Range("A3")
Wbk.Close True
Set Wbk = Nothing
End If
MsgBox "Traitement terminé..."
Cells.Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeBlanks).EntireColumn.Delete
End Sub
Private Sub Recap(ByVal Rng As Range)
Dim LastLig As Long, I As Long, j As Long, N As Long
Dim Dico As Scripting.Dictionary
Dim Tb, Tmp, Res()
Dim Str As String
Application.ScreenUpdating = False
With ThisWorkbook.Worksheets("Feuil1")
LastLig = .Cells(.Rows.Count, 1).End(xlUp).Row
Tb = .Range("A2:D" & LastLig)
End With
Set Dico = New Scripting.Dictionary
For I = 1 To LastLig - 1
Str = Tb(I, 2) & "µ" & Tb(I, 4)
If Not Dico.Exists(Str) Then
Dico.Add Str, CStr(I)
Else
Dico(Str) = Dico(Str) & ";" & CStr(I)
End If
Next I
N = Dico.Count
If N > 0 Then
ReDim Res(1 To N, 1 To 6)
For j = 1 To N
Tmp = Split(Dico.keys(j - 1), "µ")
Res(j, 1) = Tmp(0)
Res(j, 6) = Tmp(1)
Res(j, 5) = Nb(Dico.Items(j - 1))
Next j
Rng.Resize(N, 6) = Res
End If
Set Dico = Nothing
End Sub
Private Function Nb(ByVal Str As String, Optional ByVal Sep As String = ";") As Integer
Nb = Len(Str) - Len(Replace(Str, Sep, "")) + 1
End Function
Sub supprimer()
With Workbooks("program").Worksheets("Data")
Cells.Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeBlanks).EntireColumn.Delete
End With
End Sub |
Partager