This is the sample dataset.
Example 1 – Using VBA to Extract Unique Values into a Separate Column as an Array
Steps:
- Press Alt + F11 or go to Developer -> Visual Basic to open Visual Basic Editor.
- Click Insert -> Module.
- Enter the following 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
- Press F5 or select Run -> Run Sub/UserForm. You can also click the small Run icon.
This is the output.
Only unique values in the column are copied into a separate column as an array.
VBA Code Breakdown
Dim iArray As Variant
Dim RowCount As Long
Declaries the variables.
With Sheet2Selects the code name of the sheet.
Sheets("Unique").Columns("B:B").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("D2"), Unique:=True- Defines the sheet name (“Unique“).
- Defines the column that has the values (Column B).
- Performs an Advanced Filter:
- the type of Action is copy; it is defined by xlFilterCopy.
- the destination range for the copied data is D2; it is defined in CopyToRange.
- only the Unique values will be copied; it is defined by passing the boolean value True in the Unique property.
RowCount = .Cells(.Rows.Count, "B").End(xlUp).RowCounts the total row number in the dataset and stores the last row in the defined RowCount variable.
iArray = .Range("B5:B" & RowCount)Stores the range of data in Column B and the last row count as an array in the defined iArray variable.
End WithEnds the operation inside the sheet.
Dim iValue As String
Dim i As Integer
Declares variables to perform the loop.
For i = 1 To UBound(iArray)
iValue = iValue & iArray(i, 1) & ","
Next
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 the extracted value and the array with a comma (,).
Example 2 – Applying a VBA Macro to Get Unique Values from a Column into MsgBox in Excel
Steps:
- Open Visual Basic Editor in the Developer tab and Insert a Module.
- Enter the following 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
- Press F5 or select Run -> Run Sub/UserForm. You can also click the small Run icon.
The GIF below shows the result:
Only the unique values are displayed in the Excel MsgBox.
VBA Code Breakdown
Dim Arr() As String
Dim iColl As Collection
Dim iRange As Range
Dim i As Long
Declares the variables.
Set iColl = New CollectionSets the new collection/ array in the defined variable.
On Error Resume NextIf an error occurs, goes to the next statement.
For Each iRange In Worksheets("Unique Values").Range("B3:B15").Cells
iColl.Add iRange.Value, CStr(iRange.Value)
Next
Iterates through each value in B3:B15 in the worksheet (“Unique Values“). It keeps adding the extracted value in the collection by converting values into strings.
On Error GoTo 0Disables error handling.
ReDim Arr(1 To iColl.Count)Re-declares 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
Initiates a loop till the total count of the extracted collection, and adds all the values to the array.
For i = LBound(Arr) To UBound(Arr)
MsgBox Arr(i)
Next iIterates from the smallest subscript to the largest subscript in the whole array and returns the array value in the MsgBox.
MsgBox "No More Unique Values!"Pauses the code execution when there are no more unique values in the range.
Example 3 – Using a Macro to Get Unique Values as an Array from a Column into an Excel MsgBox
Steps:
- Open Visual Basic Editor in the Developer tab and Insert a Module.
- Enter the following 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
- Press F5 or select Run -> Run Sub/UserForm. You can also click the small Run icon.
This is the output.
You will get the unique values from the column only displayed in an array of values in the Excel Msgbox.
VBA Code Breakdown
iSheet = Sheets("Unique Value").Range("B3:B15")Sets the worksheet (“Unique Value“) and the range (B3:B15) in the iSheet variable.
With CreateObject("scripting.dictionary")Creates a Scripting Dictionary to store the 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 returns a new line.
End WithEnds the operation inside the sheet.
Download Practice Workbook
Download the free practice Excel workbook.
<< Go Back to Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
