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 (,).
Read More: How to Find Unique Values from Multiple Columns in Excel
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.
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 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.
Related Articles
- How to Get Unique Values in Excel
- Find Unique Values in a Column in Excel
- How to Copy Unique Values to Another Worksheet in Excel
- How to Extract Unique Items from a List in Excel
- How to Create List of Unique Values from Multiple Sheets in Excel
- How to Get Unique Values from Range in Excel
- How to Extract Unique Values Based on Criteria in Excel
