Function DerLigDesCol(ParamArray plages())
' retourne le N° de la dernière des colonnes des plages en argument ; retourne -1 si erreur
' ATTENTION ! On regarde dans les colonnes entières des arguments de la fonction
' => DerLigDesCol(Range("A1")) -> on examine toute la colonne A
' Utilisable qu'en VBA uniquement
Dim Feuille As Worksheet, xplage, xrg As Range, i As Long, der As Long
DerLigDesCol = -1
If IsMissing(plages) Then Exit Function
Set Feuille = plages(0).Parent
On Error Resume Next
With Feuille
i = 0
For Each xplage In plages
If xplage.Parent.Name <> Feuille.Name Then
MsgBox "Erreur : Plages sur feuilles différentes", vbCritical: Exit Function
End If
Set xrg = Nothing
Set xrg = xplage.EntireColumn.SpecialCells(xlCellTypeConstants, 1 + 2 + 4 + 16)
i = xrg.Areas(xrg.Areas.Count).Row + xrg.Areas(xrg.Areas.Count).Rows.Count - 1
If i > der Then der = i
Set xrg = Nothing
Set xrg = xplage.EntireColumn.SpecialCells(xlCellTypeFormulas, 1 + 2 + 4 + 16)
i = xrg.Areas(xrg.Areas.Count).Row + xrg.Areas(xrg.Areas.Count).Rows.Count - 1
If i > der Then der = i
Next xplage
End With
On Error GoTo 0
DerLigDesCol = der
End Function
Sub DeDupeColSpecific()
Dim der&, t, dico, i&, j&, clef, r, v, n&
Application.ScreenUpdating = False
With ActiveSheet
der = DerLigDesCol(.Range("a:d"))
t = .Range("a:d").Resize(der)
.Columns("a:d").Resize(der).Clear
Set dico = CreateObject("scripting.dictionary")
dico.CompareMode = TextCompare
For i = UBound(t) To 2 Step -1
clef = ""
For j = 1 To 4: clef = clef & "\" & CStr(t(i, j)) & "\": Next
If Not dico.Exists(clef) Then dico.Add clef, i
Next i
ReDim r(1 To dico.Count + 1, 1 To 4)
v = dico.Items
For j = 1 To 4: r(1, j) = t(1, j): Next
n = 1
For i = UBound(v) To LBound(v) Step -1
n = n + 1
For j = 1 To 4: r(n, j) = t(v(i), j): Next
Next i
.Columns("a:d").Resize(n) = r
.Columns("a:d").Resize(n).Borders.LineStyle = xlContinuous
End With
End Sub
ton code ne fonctionne pas dans le sens que c'est le doublon récent qui est supprimé.
je veux avoir l'inverse si possible.