VBA to remove all duplicates within range - not based on columns

pholt33

Board Regular
Joined
Jan 4, 2005
Messages
202
Office Version
  1. 365
Platform
  1. Windows
How can I remove all duplicates within a range? In the screenshot below, each blue column has had duplicates removed using the built in function, and the green range is after I manually selected each unique value from the blue.
What I really want is to be able to select a variable-sized range and end up only one instance of each value. Having the results in a single list like the green would be great but it would also be fine if the result was like the second screenshot where the uniques are in their original location.

Range.jpg



Results.jpg
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Can you post your data using the XL2BB tool? It will make it so much easier to copy and work with that data in order to provide you a solution. Thanks!
 
Upvote 0
How about
VBA Code:
Sub pholt()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Selection
         If Cl.Value <> "" Then .Item(Cl.Value) = Empty
      Next Cl
      
      Selection.Offset(, Selection.Columns.Count + 1).Resize(.Count, 1).Value = Application.Transpose(.Keys)
   End With
End Sub
This will work on whatever cells are selected.
 
Upvote 1
Can you post your data using the XL2BB tool? It will make it so much easier to copy and work with that data in order to provide you a solution. Thanks!
Unfortunately, my work won't allow me to install any add-ins. Hopefully this will suffice...


FD1622FD1622FD1622FD1582FD1582
FD1582FD1608FD1608FD1606FD1606
FD1608FD1582FD1582FD1599FD1599
FD2743FD4571FD4571FD2623FD2623
FD2623FD1606FD1606FD1608FD1608
FD1004FD2623FD2623FD1620FD1620
FD1624FD1599FD1599FD4507FD4507
FD1003FD1003FD1583FD1583
FD1611FD1611FD1622FD1622
--FD1621FD1621
FD1628FD1628
FD1578FD1578
FD1004FD1004
--
 
Upvote 0
How about
VBA Code:
Sub pholt()
   Dim Cl As Range
  
   With CreateObject("scripting.dictionary")
      For Each Cl In Selection
         If Cl.Value <> "" Then .Item(Cl.Value) = Empty
      Next Cl
     
      Selection.Offset(, Selection.Columns.Count + 1).Resize(.Count, 1).Value = Application.Transpose(.Keys)
   End With
End Sub
This will work on whatever cells are selected.
That is awesome and way more streamlined than I would have expected.

Can you walk me through what it is actually doing?
 
Upvote 0
It loops through the selected cells & if the cell isn't blank it adds it to the dictionary if it's not already in there.
For more about dictionaries have a look here Excel VBA Dictionary - A Complete Guide - Excel Macro Mastery

This could also be done quite simply with a formula depending on your version of Excel.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top