Populate an array with no duplicates

[austin350s10]

I am trying to create an array that references a range to fill the array. The problem I am running into is that the reference range contains a lot of duplicate values and I only need unique values added into the array. Does anyone have a good sample code that can either remove duplicates from an array or make sure duplicates are not added in the first place?

Sample:
Reference Range
A1 = test
A2 = not a test
A3 = test
A4 = def not a test

Ideal Array Output
myArray = ("test", "not a test", "def not a test")

 

[hiker95]

austin350s10,

Sample raw data:

Excel 2007
	A	B	C
1	test
2	not a test
3	test
4	def not a test
5
Sheet1

After the macro:

Excel 2007
	A	B	C
1	test		test
2	not a test		not a test
3	test		def not a test
4	def not a test
5
Sheet1

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub GetUniques()
' hiker95, 08/19/2014, ME799955
Dim c As Range, myArray
With CreateObject("Scripting.Dictionary")
  .CompareMode = vbTextCompare
  For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
    If c <> "" Then
      If Not .Exists(c.Value) Then
        .Add c.Value, c.Value
      End If
    End If
  Next c
  myArray = Application.Transpose(Array(.Keys))
End With
Range("C1").Resize(UBound(myArray)) = myArray
Columns(3).AutoFit
End Sub

You may have to add the Microsoft Scripting Runtime to the References - VBA Project.

With your workbook that contains the above:

Press the keys ALT + F11 to open the Visual Basic Editor

In the VBA Editor, click on:
Tools
References...

Put a checkmark in the box marked
Microsoft Scripting Runtime

Then click on the OK button.

And, exit out of the VBA Editor.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetUniques macro.

 

[austin350s10]

Thank you! Works like a charm!!

 

[hiker95]

austin350s10,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.