Using VBA to Get Unique Values from a Column into an Array in Excel – 3 Examples

VBA Code Breakdown

Dim iArray As Variant
Dim RowCount As Long

Declaries the variables.

With Sheet2

Selects 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).Row

Counts 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 With

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

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 Collection

Sets the new collection/ array in the defined variable.

On Error Resume Next

If 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 0

Disables 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 i

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

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 With

Ends the operation inside the sheet.