Populate array with unique values from a filtered column with multiple values

Question

I am trying to populate an array with values from a column in a filtered Excel table.

In this column, the values may appear multiple times, but I need to return unique values, not all occurrences of each value.

Column F
a
a
a
b
c
c
a
b
d

The array would have a variable length and, based on the sample column would have the elements: {a, b, c, d}

The length of the array cannot be fixed because my function works with a filtered table that varies in length. Sometimes there may be only one unique value, other times there might be three.

I need to do this because my array will be used to determine the subject of an e-mail with, "... " & Array.

How do I extract unique values in a column to the array?

I would use a collection and loop through using xlCellTypeVisible more here. For each item check if it doesn't exist, then add. — DDV
– DDV, Commented Oct 9, 2019 at 9:17
Could you please use the edit link to show what you've tried? (Such as creating the array with all the values.) That would give people a starting point to build on demonstrating the part that you're missing... — Cindy Meister
– Cindy Meister, Commented Oct 9, 2019 at 9:31
You may copy the filtered value to another range and use RemoveDuplicates to get unique values — Dhirendra Kumar
– Dhirendra Kumar, Commented Oct 9, 2019 at 9:43

Louis · Accepted Answer · 2019-10-09 10:21:31Z

2

Use Scripting.Dictionary

You can select all the data from a column with sht.Cells(sht.Rows.Count, "F").End(xlUp).Row and then use a Scripting.Dictionary to find your unique values. Here's the code:

'Main Routine
Sub MyMacro()
    Dim sht As Worksheet
    Dim column As Range
    Dim LastRow As Long
    Dim uniqueValues() As Variant

    Set sht = ActiveSheet 'Set your sheet here

    LastRow = sht.Cells(sht.Rows.Count, "F").End(xlUp).Row
    Set column = Range("F1:F" & LastRow)
    uniqueValues = getUniqueValues(column)
    'Do what you need to do with your values [...]
End Sub

'Return unique values from a Range
Function getUniqueValues(column As Range)
    Dim dict As New Scripting.Dictionary ' requires "Microsoft Scripting Runtime"
    Dim cell As Range

    For Each cell In column
        dict(cell.Value) = "1"
    Next

    'A double Transpose will put your data in an Array() format
    getUniqueValues = Application.Transpose(Application.Transpose(dict.Keys))
End Function

If you don't want to import Microsoft Scripting Runtime, use this code for dict declaration:

    'If you don't want to import Scripting Runtime, use this code
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")

Note: This is tested and works perfectly.

I hope this helps.

edited Oct 9, 2019 at 10:21

answered Oct 9, 2019 at 9:51

Louis

3,6222 gold badges13 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gianluca Over a year ago

Thank you for your solutions, it works as well as the one below. I simply used the second proposal because I understand it better but I thank you anyway because I tried yours too and it works.

Vasily · Accepted Answer · 2019-10-09 09:43:00Z

1

you can use Scripting.Dictionary for such task and xlCellTypeVisible, example:

Sub sometest()
    Dim x As Long, cl As Range
    Dim dic As Object: Set dic = CreateObject("Scripting.Dictionary")
    dic.comparemode = vbTextCompare

    x = Cells(Rows.Count, "A").End(xlUp).Row

    For Each cl In Range(Cells(2, "A"), Cells(x, "A")).SpecialCells(xlCellTypeVisible)
        If Not dic.exists(cl.Value) Then
            dic.Add cl.Value, Nothing
        End If
    Next cl

    Debug.Print Join(dic.keys, ",")

End Sub

test:

answered Oct 9, 2019 at 9:43

Vasily

5,7923 gold badges22 silver badges36 bronze badges

4 Comments

DDV Over a year ago

Why not use Dim dic As Scripting.Dictionary instead of Dim dic As Object: Set dic = CreateObject("Scripting.Dictionary")?

DDV Over a year ago

Also make OP aware that they will need to enable Microsoft Scripting Runtime in References in the VBE if using a dictionary.

Vasily Over a year ago

@DeanDeVilliers there is no necessary of enabling Microsoft Scripting Runtime in References when used such approach of declaration Dim dic As Object: Set dic = CreateObject("Scripting.Dictionary"). Your proposed approach of declaration Dim dic As Scripting.Dictionary require additional steps of enabling Microsoft Scripting Runtime in References

Gianluca Over a year ago

Thank you Vasily, your solutions worked just fine. Now I will try to understand how does it work, but I copied your code and it works.

Collectives™ on Stack Overflow

Populate array with unique values from a filtered column with multiple values

2 Answers 2

Use Scripting.Dictionary

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Use Scripting.Dictionary

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related