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.
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 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
       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 SubYour code is now ready to run.
- 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 Sheet2Selecting the code name of the sheet that we will be working on.
Sheets("Unique").Columns("B:B").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("D2"), Unique:=TrueThis 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).RowTo 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 WithEnds 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
       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 SubYour code is now ready to run.
- 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 CollectionTo set the new collection/ array in the defined variable.
On Error Resume NextIf 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 0To 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 iThis 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.
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 SubYour code is now ready to run.
- 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 WithEnds performing the operation inside the sheet.
Download Practice Workbook
You can download the free practice Excel workbook from here.
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.
<< Go Back to Unique Values | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
