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

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.


Download Workbook

You can download the free practice Excel workbook from here.


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

Following this section, you will know how to get unique values from a column as an array into a separate column and into Excel MsgBox with VBA macro.

Dataset for excel vba to get unique values from column into array

Above is the example dataset that this article will follow to describe the methods.


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 that 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
        Sheets("Unique").Columns("B:B").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("D2"), Unique:=True
        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

Your code is now ready to run.

Excel VBA to get unique values from column into array

  • 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.

Result of excel vba to get unique values from column into array

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 (,).

Read More: How to Extract Unique Items from a List in Excel (10 Methods)


Similar Readings


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
        iColl.Add iRange.Value, CStr(iRange.Value)
    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

Your code is now ready to run.

Excel VBA to get unique values from column into array in MsgBox

  • 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
    iColl.Add iRange.Value, CStr(iRange.Value)
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: How to Get Unique Values from Range in Excel (8 Methods)


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

Your code is now ready to run.

Excel VBA to get unique values from column as an array into MsgBox

  • Later, Run the macro and look at the following image to see the output.

Result of Excel VBA to get unique values from column as an array into MsgBox

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.

Read More: How to Find Unique Values from Multiple Columns in Excel


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.


Related Articles

Sanjida Ahmed

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

Leave a reply

ExcelDemy
Logo