Hi,
I have a excel with multiple columns where in column T I have some names with duplicates say A.pdf,B.pdf,C.pdf,A.pdf,A.pdf,A.pdf
I want them on this way using macro code
A.pdf,B.pdf,C.pdf,A_1.pdf,A_2.pdf,A_3.pdf
Any help would be appreciated
Renaming duplicate column names
- SUBHASHn
- Thread is marked as Resolved.
-
-
An example workbook would help.
-
I asked fr an example workbook!
-
-
Try:
Code
Display MoreSub ReName() Application.ScreenUpdating = False Dim LastRow As Long, rng As Range, key As Variant, x As Long LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row arr = Range("G2:G" & LastRow).Value Set dic = CreateObject("Scripting.Dictionary") For i = 1 To UBound(arr, 1) If Not dic.Exists(arr(i, 1)) Then dic.Add arr(i, 1), Nothing End If Next i For Each key In dic.keys Range("G1").AutoFilter Field:=1, Criteria1:=key For Each rng In Range("G2:G" & LastRow).SpecialCells(xlCellTypeVisible) If x = 0 Then rng.Offset(, 1) = rng x = x + 1 Else rng.Offset(, 1) = Left(rng, WorksheetFunction.Find(".", rng) - 1) & "_" & x & ".pdf" x = x + 1 End If Next rng x = 0 Next key Range("G1").AutoFilter Application.ScreenUpdating = True End Sub -
When I tested the macro on the file you posted, it worked properly. If you are using the macro on a different file, please post a copy of the file that is giving you the error. Which version of Excel are you using?
-
-
Glad it worked out.
-
-
Those lines of code are necessary for the macro to work properly.
-
You are very welcome.
-
PFA
-
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!