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 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100
|
Option Explicit
Sub M100_DupliquerLesLignes()
Dim I As Long, LigneEncours As Long, LigneTitre As Long, DerniereLigne As Long, ColIndice As Long, ColRepere As Long
Dim AireATester As Range, CelluleATester As Range
Dim J As Integer, NbRepetitions As Integer
With ActiveSheet
Application.ScreenUpdating = False
LigneTitre = 10
ColIndice = .Cells(LigneTitre, .Columns.Count).End(xlToLeft).Column + 1
ColRepere = 6
DerniereLigne = .Cells(.Rows.Count, ColRepere).End(xlUp).Row
LigneEncours = DerniereLigne + 1
Set AireATester = .Range(.Cells(LigneTitre + 1, ColRepere), .Cells(DerniereLigne, ColRepere))
I = 1
.Cells(LigneTitre, ColIndice) = "Indice"
For Each CelluleATester In AireATester
CelluleATester.Offset(0, ColIndice - ColRepere) = I
I = I + 1
Next CelluleATester
For Each CelluleATester In AireATester
NbRepetitions = UBound(ListeEntreVirgules(CelluleATester.Value))
If NbRepetitions > 0 Then
For J = 1 To NbRepetitions
CelluleATester.EntireRow.Copy Destination:=.Cells(LigneEncours, 1)
LigneEncours = LigneEncours + 1
Next J
End If
Next CelluleATester
TrierUnTableau ActiveSheet, LigneTitre, ColIndice
DerniereLigne = .Cells(.Rows.Count, ColRepere).End(xlUp).Row
.Range(.Cells(LigneTitre, ColIndice), .Cells(DerniereLigne, ColIndice)).Clear
Application.ScreenUpdating = True
MsgBox "Fin de la duplication !", vbInformation
End With
Set AireATester = Nothing
End Sub
Function ListeEntreVirgules(ByRef ContenuCelluleG As String) As Variant
ListeEntreVirgules = Split(ContenuCelluleG, ",")
End Function
Sub TrierUnTableau(ByVal FeuilleATrier As Worksheet, ByVal LigneDeTitre As Long, ByVal ColonneATrier As Long)
Dim DerniereColonne As Long
Dim DerniereLigne As Long
Dim AireATrier As Range
Dim AireColonne As Range
With FeuilleATrier
DerniereColonne = .Cells(LigneDeTitre, .Columns.Count).End(xlToLeft).Column
DerniereLigne = .Cells(.Rows.Count, ColonneATrier).End(xlUp).Row
If DerniereLigne > LigneDeTitre Then
Set AireATrier = .Range(.Cells(LigneDeTitre, 1), .Cells(DerniereLigne, DerniereColonne))
Set AireColonne = .Range(.Cells(LigneDeTitre, ColonneATrier), .Cells(DerniereLigne, ColonneATrier))
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=AireColonne, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With .Sort
.SetRange AireATrier
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Set AireColonne = Nothing
Set AireATrier = Nothing
End If
End With
End Sub |
Partager