How to Get All Unique Values Using VBA

Working with VBA is used today mostly to automate processes. Sometimes, however, it can be easier to do the work itself in the standard way.

With Excel Office 365, a couple of cool options for arrays were introduced. One of them is the UNIQUE formula, which lets us extract unique values from a particular range.

This, of course, can be done with VBA as well. In the example below, we will show how to do it.

Get Unique Values Using VBA

To write the code in VBA, we first need to open it. We can do it by clicking ALT + F11 on our keyboard to open the VBA, then right-click on the left window and select Insert >> Module:

Once there, we can set the format of our cells with the following code:

Sub Uniques()
    'declaring variables
    Dim o As Object, z As Variant, k As Long, lr As Long
    'setting values
    Set o = CreateObject("Scripting.Dictionary")
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    z = Range("A2:A" & lr)
    'for next loop
    For k = 1 To UBound(z, 1)
        o(z(k, 1)) = 1
    Next k
    'transposing values
    Range("B2").Resize(o.Count) = Application.Transpose(o.keys)
End Sub

Sub Uniques()

'declaring variables

Dim o As Object, z As Variant, k As Long, lr As Long

'setting values

Set o = CreateObject("Scripting.Dictionary")

lr = Cells(Rows.Count, 1).End(xlUp).Row

z = Range("A2:A" & lr)

'for next loop

For k = 1 To UBound(z, 1)

o(z(k, 1)) = 1

Next k

'transposing values

Range("B2").Resize(o.Count) = Application.Transpose(o.keys)

End Sub

This is what our code looks like in the module itself:

The code presented above will take all the values in column A and will paste only the unique values from column A to column B. You can define the different ranges to be copied and different destination ranges, depending on your needs.

We will insert a couple of values in our worksheet (column A) just to show that the code works after execution:

The next step is to go into our code and press F5 to execute it. After that, this is the result we get in column B:

Just to be clear, the text in the first row has been added manually, not in the code.

Although we’ve left comments in every segment of the code, we will explain it part by part.

For the first thing, we declare our variables:

'declaring variables
Dim o As Object, z As Variant, k As Long, lr As Long

1 2	'declaring variables Dim o As Object, z As Variant, k As Long, lr As Long

Then we need to assign them to certain values:

'setting values
Set o = CreateObject("Scripting.Dictionary")
lr = Cells(Rows.Count, 1).End(xlUp).Row
z = Range("A2:A" & lr)

'setting values

Set o = CreateObject("Scripting.Dictionary")

lr = Cells(Rows.Count, 1).End(xlUp).Row

z = Range("A2:A" & lr)

Scripting.Dictionary functions in a way that it stores data, in any form, in the array. Our lr variable counts the number of populated cells in column A, while variable z stores these values.

Next thing, we create our For Next Loop:

'for next loop
For k = 1 To UBound(z, 1)
    o(z(k, 1)) = 1
Next k

'for next loop

For k = 1 To UBound(z, 1)

o(z(k, 1)) = 1

Next k

In the loop, we are setting boundaries from 1 to whatever number of cells is stored in the z range, and then we store these variables in our o array.

For the final part:

'transposing values
Range("B2").Resize(o.Count) = Application.Transpose(o.keys)

1 2	'transposing values Range("B2").Resize(o.Count) = Application.Transpose(o.keys)

We resize the o array and transpose only unique values to column B. Notice that we defined in which exact cell will the values be copied.

And that is it. The beauty of this code is that we can change the desired range to be looked at, and also the desired location where the unique values will be copied.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Post Views: 2,224