# VBA to Get Unique Values from Column into Array in Excel (3 Criteria)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes we want to extract unique values from the dataset of our Excel workbook; But when the dataset is huge, it is hard to find the values. Implementing VBA is the most effective, quickest, and safest method to run any operation in Excel. In this article, we will show you 3 effective criteria on how to get unique values from a column into an array in Excel with the VBA macro.

## VBA to Get Unique Values from Column into Array in Excel: 3 Criteria

Following this section, you will know how to find unique values from a column as an array into a separate column and into Excel MsgBox with VBA macro. ### 1. Embed VBA to Extract Unique Values into a Separate Column as an Array

In this section, we will show you how you can get only the unique values from the given column of repetitive data into another column as an array of data with VBA in Excel.

The steps to execute this are given below.

Steps:

• In the beginning, press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor. • Next, in the pop-up code window, from the menu bar, click Insert -> Module. • Then, copy the following code and paste it into the code window.
``````Sub UniqueValuesCopy()
Dim iArray As Variant
Dim RowCount As Long
With Sheet2
RowCount = .Cells(.Rows.Count, "B").End(xlUp).Row
iArray = .Range("B5:B" & RowCount)
End With
Dim iValue As String
Dim i As Integer
For i = 1 To UBound(iArray)
iValue = iValue & iArray(i, 1) & ","
Next
End Sub`````` • Now, press F5 on your keyboard, or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Run icon in the sub-menu bar to run the macro. After successful code execution, look at the following image to check out the result. As a result, only the unique values from the given column of repetitive values are copied in a separate column as an array of values.

VBA Code Explanation

``````Dim iArray As Variant
Dim RowCount As Long
``````

Declaring the variables.

``With Sheet2``

Selecting the code name of the sheet that we will be working on.

``Sheets("Unique").Columns("B:B").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("D2"), Unique:=True``

This line of code:

• First, defines the sheet name (“Unique” is the sheet name in our dataset).
• Then, defines the column that has the values (Column B holds the values in our worksheet).
• Then, performs Advanced Filter. The type of the Advanced Filter operation will be as follows:
• type of the Action will be to copy; we define this by providing xlFilterCopy inside the code.
• the destination range for the copied data will be Cell D2; we define this in the CopyToRange property.
• only the Unique values will be copied; we define this by passing the boolean value True in the Unique property.
``RowCount = .Cells(.Rows.Count, "B").End(xlUp).Row``

To count the total row number in the dataset and store the last row in the defined RowCount variable.

``iArray = .Range("B5:B" & RowCount)``

To store the range of the data from Column B and the last row count as an array in the defined iArray variable.

``End With``

Ends performing the operation inside the sheet.

``````Dim iValue As String
Dim i As Integer
``````

Again, declaring variables to perform the loop.

``````For i = 1 To UBound(iArray)
iValue = iValue & iArray(i, 1) & ","
Next
``````

This piece of code starts the FOR loop with the array. It continues to loop until it returns the largest subscript of the array. Once it finds the value, the code stores it in the defined iValue variable, concatenating them (the extracted value and the array) with a comma (,).

### 2. Apply VBA Macro to Get Unique Values from Column into MsgBox in Excel

In this section, you will learn how to get the unique values from the column into Msgbox in Excel with VBA macro.

Let’s see the steps to execute that.

Steps:

• Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Then, copy the following code and paste it into the code window.
``````Option Explicit
Private Sub cmdSummary_Click()
Dim Arr() As String
Dim iColl As Collection
Dim iRange As Range
Dim i As Long
Set iColl = New Collection
On Error Resume Next
For Each iRange In Worksheets("Unique Values").Range("B3:B15").Cells
Next
On Error GoTo 0
ReDim Arr(1 To iColl.Count)
For i = 1 To iColl.Count
Arr(i) = iColl.Item(i)
Next i
For i = LBound(Arr) To UBound(Arr)
MsgBox Arr(i)
Next i
MsgBox "No More Unique Values!"
End Sub`````` • After that, Run the macro as we showed you in the above section. The result is shown in the gif below. As you can see from the above GIF, only the unique values are displayed in the Excel MsgBox.

VBA Code Explanation

``````Dim Arr() As String
Dim iColl As Collection
Dim iRange As Range
Dim i As Long
``````

Declaring the variables.

``Set iColl = New Collection``

To set the new collection/ array in the defined variable.

``On Error Resume Next``

If an error occurs then go to the next statement.

``````For Each iRange In Worksheets("Unique Values").Range("B3:B15").Cells
Next
``````

This piece of code is here for iterating through each value in the range (B3:B15 that holds the values) of the worksheet (“Unique Values” is the sheet name) in our dataset.  Then, it keeps adding the extracted value in the collection by converting them into strings.

``On Error GoTo 0``

To disable error handling in the existing procedure.

``ReDim Arr(1 To iColl.Count)``

Re-declaring the array from 1 to the total count of the extracted value in the collection.

``````For i = 1 To iColl.Count
Arr(i) = iColl.Item(i)
Next i
``````

This section of the code initiates a loop till the total count of the extracted collection and adds them inside the array. It continues to do that until it finishes adding all the values inside the array.

``````For i = LBound(Arr) To UBound(Arr)
MsgBox Arr(i)
Next i``````

This part of the code starts iterating from the smallest subscript to the largest subscript of the array and throws the array value in the MsgBox. It continues doing this until it finishes scanning through the whole array.

``MsgBox "No More Unique Values!"``

This line here is just for pausing the code execution. To let the user know that after extracting all the unique values from the given column, there are no more unique values in the range.

Read More: Excel VBA Get Unique Values from Column

### 3. Implement Macro to Get Exclusive Values as Array from Column into Excel MsgBox

In the previous section, you have seen how to get only the exclusive values one by one into MsgBox. But in this section, you will learn how to get the unique values from the column all at once into Excel Msbox as an array with VBA.

The steps to get that are given below.

Steps:

• As shown before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Then, copy the following code and paste it into the code window.
``````Sub UniqueValues()
iSheet = Sheets("Unique Value").Range("B3:B15")
With CreateObject("scripting.dictionary")
For Each iData In iSheet
Arr = .Item(iData)
Next
MsgBox Join(.keys, vbLf)
End With
End Sub`````` • Later, Run the macro and look at the following image to see the output. As a result of the successful VBA code execution, you will get only the unique values from the given column of repetitive values into an array of values in the Excel Msgbox.

VBA Code Explanation

``iSheet = Sheets("Unique Value").Range("B3:B15")``

To set the worksheet (“Unique Value” is the sheet name in our dataset) and the range (B3:B15 that holds the values) in the iSheet variable.

``With CreateObject("scripting.dictionary")``

Creates Scripting Dictionary to store array.

``````For Each iData In iSheet
Arr = .Item(iData)
Next
``````

Starts looping for each item in the sheet. Once it finds the item, it stores it in the Arr array variable. It continues looping until it finishes scanning all the items.

``MsgBox Join(.keys, vbLf)``

Returns a joined array of the substring that contains all existing keys in the defined Dictionary object. After each array, it throws a new line.

``End With``

Ends performing the operation inside the sheet.

## Conclusion

To conclude, this article showed you 3 effective criteria on how to get unique values from a column into an array in Excel with the VBA macro. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  