dragonfire33
Board Regular
- Joined
- Oct 7, 2021
- Messages
- 90
- Office Version
- 365
- Platform
- Windows
Buenos días como puedo mejorar el procedimiento de el siguiente código
VBA Code:
Sub Repetidos()
'Por.Dante Amor
col = "TG"
'
Application.ScreenUpdating = False
c = Columns(col).Column
Range(Cells(1, c), Cells(1, c + 2)).EntireColumn.ClearContents
For Each n In Range("A1:SZ217").SpecialCells(xlCellTypeConstants, 23)
Set b = Columns(c).Find(n.Value, lookat:=xlWhole)
If Not b Is Nothing Then
Cells(b.Row, c + 1) = Cells(b.Row, c + 1) + 1
Cells(b.Row, c + 2) = Cells(b.Row, c + 2) & ", " & n.Address(False, False)
Else
u = Range(col & Rows.Count).End(xlUp).Row + 1
Cells(u, c) = n.Value
Cells(u, c + 1) = 1
Cells(u, c + 2) = n.Address(False, False)
End If
Next
For i = u To 1 Step -1
If Cells(i, c + 1) = 1 Then
Range(Cells(i, c), Cells(i, c + 2)).Delete Shift:=xlUp
End If
Next
'
With ActiveSheet.Sort
.SortFields.Clear
.SortFields.Add Key:=Range(Cells(1, c + 1), Cells(u, c + 1)), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
.SetRange Range(Cells(1, c), Cells(u, c + 2))
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Application.ScreenUpdating = True
MsgBox "Fin"
End Sub