Removing duplicates in collection

When Microsoft is publishing new version of Excel they add new tools to Excel. I think in newest version of Excel there is duplicates remover. In Excel 2003 there wasn’t such a tool – I think there wasn’t? Few years ago writing application to support automatic warehouse there was need to extract unique batch numbers.

I don’t remember from where code has been taken – but I think it has been copied from some book of John Walkenbach. Following code is removing column’s values duplicates by adding them to new collection. To prevent error showing On Error Resume Next must be added to code. It’s known that used loop means long waiting time when there is a lot of not empty cells in column.

Removing duplicates:

Private Sub AddNextSheets()
' CREATE NEW SHEETS ON THE BASIS OF
' UNIQUE VALUES FROM SELECTED COLUMN

Dim LicznikWierszy As Long
Dim RemoveDuplicates As New Collection
Dim NewFileName as string

NewFileName "Some Excel file name"

LicznikWierszy = 10
Do Until ThisWorkbook.Sheets("SheetName").Cells(LicznikWierszy, 6) = ""

    If ThisWorkbook.Sheets("SheetName").Cells(LicznikWierszy, 6) <> "" Then
        ' When cell is not empty then is adding to collection
        On Error Resume Next
        ' Removing duplicates
        RemoveDuplicates.Add ThisWorkbook.Sheets("SheetName").Cells(LicznikWierszy, 6), _
        CStr(ThisWorkbook.Sheets("SheetName").Cells(LicznikWierszy, 6))
        On Error GoTo 0
    Else
        ' Empty cell - nothing happened
    End If
LicznikWierszy = LicznikWierszy + 1
Loop

' Adding new sheets to chosen file with name from RemoveDuplicates collection
Windows(NewFileName).Activate
Dim Item As Object
For Each Item In RemoveDuplicates
    ' Adding sheet at end of WorkSheet
    Sheets.Add After:=Workbooks(NewFileName).Sheets(Workbooks(NewFileName).Sheets.Count)
    ' Name change
    Workbooks(NewFileName).Sheets(Workbooks(NewFileName).Sheets.Count).Name = Item & "-AA"
Next

110 thoughts on “Removing duplicates in collection

  1. Pingback: 토토도메인

  2. Pingback: 파워볼이브

  3. Pingback: buy/order hydrocodone online pharmacy legally cheap no script for pain, anxiety from USA UK Canada Australia Europe overseas overnight delivery

  4. Pingback: cach soi keo bong da

  5. Pingback: order xanax online in usa canada uk australia without prescription nextday shipping

  6. Pingback: danh de online uy tin

  7. Pingback: mo thay cho danh con gi

  8. Pingback: Pi News Wire

  9. Pingback: thenaturalpenguin

  10. Pingback: keo chap

  11. Pingback: mơ rắn đánh đề con gì

  12. Pingback: English and French bulldog puppies for sale near me in USA Canada Uk Australia Europe cheap

  13. Pingback: UNICC CM

  14. Pingback: fake watches

  15. Pingback: long blonde wig

  16. Pingback: buy/order Orange Creamsicle Creampop marijuana Edibles online use for pain, anxiety, sleep for sale near me bulk in usa uk nz canada australia overnight delivery

  17. Pingback: Hotels near Anaheim Train Station

  18. Pingback: eatverts

  19. Pingback: buy/order percocet 10/325 30mg 15mg online pharmacy legally no script for pain anxiety weight loss in USA Canada UK Australia overseas overnight delivery

  20. Pingback: immediate edge reviews

  21. Pingback: buy rishi turkey tail Magic Mushroom pills Capsules trip drug online for sale near me in USA Canada UK Australia overnight delivery cheap

  22. Pingback: Order Adderall 10mg online overnight delivery

  23. Pingback: Immediate Edge

  24. Pingback: frontier airlines Phone Number

  25. Pingback: Plumber Near Me

  26. Pingback: bitcoin loophole review 2020

  27. Pingback: mơ thấy con mèo đánh con gì

  28. Pingback: mơ thấy ba ba

  29. Pingback: Bichon Frise Puppies

  30. Pingback: mơ thấy người cha đã mất

  31. Pingback: Keltec Firearms for Sale

  32. Pingback: Topcamtube.com

  33. Pingback: visit website

  34. Pingback: ตู้แปลภาษา

  35. Pingback: mo gap ran

  36. Pingback: nằm mơ thấy nhà sập

  37. Pingback: Rock Island Armory VR80 12ga Shotgun

  38. Pingback: male sex dolls for sale

  39. Pingback: BP-12

  40. Pingback: con cóc số mấy

  41. Pingback: mơ thấy người khác chết đuối

  42. Pingback: mơ thấy gấu

  43. Pingback: nam mo thay ngo

  44. Pingback: mơ câu được cá chép

  45. Pingback: nằm mơ thấy nhà mới

  46. Pingback: centralqq

  47. Pingback: dơi bay vào nhà tốt hay xấu

  48. Pingback: Stiiizy pod

  49. Pingback: buy cialis overnight

  50. Pingback: safe to buy levitra online

  51. Pingback: cialis ordering australia

  52. Pingback: cialis ordering

  53. Pingback: viagra 100mg generic

  54. Pingback: buy cialis philippines

  55. Pingback: find cheap cialis online

  56. Pingback: ordering cialis online australia

  57. Pingback: cost of tadalafil without insurance

  58. Pingback: can i buy cialis in uk

  59. Pingback: cialis 20mg for sale

  60. Pingback: yhoyhpmy

  61. Pingback: where can i buy cheap viagra online

  62. Pingback: hoe gevaarlijk is viagra

  63. Pingback: how long does it take for zithromax to dissolve

  64. Pingback: cheap generic cialis

  65. Pingback: chew or swallow sildenafil

  66. Pingback: buy cialis

  67. Pingback: generic viagra

  68. Pingback: ordering cialis online australia

  69. Pingback: can i buy cialis in toronto

  70. Pingback: viagra

  71. Pingback: buy cialis with paypal

  72. Pingback: college application essay writing service

  73. Pingback: research paper writer

  74. Pingback: write my essay for me online

  75. Pingback: essay on the help

  76. Pingback: power v8 viagra

  77. Pingback: taking cialis soft tabs

  78. Pingback: taking cialis soft tabs

  79. Pingback: where can i buy amoxocillin

  80. Pingback: furosemide 20 mg tablet cost

  81. Pingback: zithromax online purchase

  82. Pingback: ivermectin over the counter canada

  83. Pingback: albuterol 990 mcg

  84. Pingback: doxycycline for abscess

  85. Pingback: prednisolone in pregnancy

  86. Pingback: clomid mood swings

  87. Pingback: order priligy

  88. Pingback: diflucan tablet dosage

  89. Pingback: who manufactures synthroid

  90. Pingback: merck propecia coupons

  91. Pingback: neurontin 200 mg

  92. Pingback: insulin vs metformin

  93. Pingback: paxil tired

  94. Pingback: plaquenil stopping

  95. Pingback: cialis sale 20mg

  96. Pingback: buy cialis online in canada

  97. Pingback: 20 mg cost

  98. Pingback: Husband On Tinder

  99. Pingback: best payday loans denver

  100. Pingback: free malta dating sites

  101. Pingback: best tadalafil tablets

  102. Pingback: viagra

  103. Pingback: cialis on ebay

  104. Pingback: sildenafil 100mg cost

  105. Pingback: define cialis

  106. Pingback: viagra overnight

  107. Pingback: tadalafil natural substitute

  108. Pingback: online viagra

  109. Pingback: price of tadalafil

  110. Pingback: sildenafil 100mg gel

Leave a Reply

Your email address will not be published. Required fields are marked *