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

pholt33

Board Regular
Joined
Jan 4, 2005
Messages
177
Office Version
  1. 2016
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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Crystalyzer

Well-known Member
Joined
Oct 18, 2011
Messages
688
Office Version
  1. 365
Platform
  1. Windows
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!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,119
Office Version
  1. 365
Platform
  1. Windows
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.
 

pholt33

Board Regular
Joined
Jan 4, 2005
Messages
177
Office Version
  1. 2016
Platform
  1. Windows
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
--
 

Crystalyzer

Well-known Member
Joined
Oct 18, 2011
Messages
688
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thanks @pholt33 but it seems like @Fluff has provided a very elegant solution already!! :) Good luck!
 

pholt33

Board Regular
Joined
Jan 4, 2005
Messages
177
Office Version
  1. 2016
Platform
  1. Windows
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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,119
Office Version
  1. 365
Platform
  1. Windows
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’)
 

Forum statistics

Threads
1,144,663
Messages
5,725,658
Members
422,635
Latest member
crisis

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
Top