Extract Unique Values with Excel VBA

Objective:

The goal of this VBA code is to extract unique values from a specified column of data and output them in another column (or even on a different sheet). By « unique values, » we mean only distinct entries, without duplicates.

Step-by-Step Explanation:

1. Understanding the Task:
   • In an Excel sheet, data might contain duplicates, which could make it harder to analyze.
   • The goal of this code is to extract only unique values from a given range of data.
   • For example, if you have a list of names and some names repeat, this code will output only the distinct names.
2. Basic Logic of the Code:
   • Identify the range of data that contains the values (let’s assume it’s in Column A).
   • Use a collection (a data structure) to store only unique values from this column.
   • The reason we use a collection is that collections in VBA automatically discard duplicate values when you attempt to add them, making it an efficient way to keep track of unique values.
   • Once all the unique values are extracted, we will output them to another column (e.g., Column B).

VBA Code for Extracting Unique Values

Sub ExtractUniqueValues()
    ' Declare necessary variables
    Dim sourceRange As Range
    Dim outputRange As Range
    Dim uniqueCollection As Collection
    Dim cell As Range
    Dim item As Variant
    Dim lastRow As Long
    Dim outputRow As Long   
    ' Set the source range (adjust as needed)
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row ' Get last row of data in Column A
    Set sourceRange = Range("A1:A" & lastRow) ' Define the source range from A1 to the last row   
    ' Create a new collection to store unique values
    Set uniqueCollection = New Collection   
    ' Loop through the source range and add unique values to the collection
    On Error Resume Next ' Ignore errors when trying to add duplicate values to the collection
    For Each cell In sourceRange
        If cell.Value <> "" Then ' Check if the cell is not empty
            uniqueCollection.Add cell.Value, CStr(cell.Value) ' Use the value as both item and key (key must be unique)
        End If
    Next cell
    On Error GoTo 0 ' Turn off the error handler   
    ' Set the output range starting at B1 (or any other location)
    Set outputRange = Range("B1")
    outputRow = 1 ' Start outputting from row 1 in Column B   
    ' Loop through the collection and output unique values
    For Each item In uniqueCollection
        outputRange.Cells(outputRow, 1).Value = item
        outputRow = outputRow + 1 ' Move to the next row for output
    Next item   
    ' Notify user the task is complete
    MsgBox "Unique values have been extracted successfully!", vbInformation   
End Sub

Code Explanation:

1. Declare Variables:
   • sourceRange: This variable will hold the range of cells from which we want to extract unique values. It’s the column where you have the initial data.
   • outputRange: This variable specifies where we want to output the unique values. You can modify this to place the unique values anywhere in your worksheet.
   • uniqueCollection: A Collection object that will store only the unique values. The Collection object in VBA does not allow duplicates when you try to add an item using the Add method. We will leverage this behavior.
   • cell: A Range object used to loop through each cell in the source range.
   • item: A variable to hold the item (unique value) while looping through the collection.
   • lastRow: This will determine the last row with data in column A. This ensures we don’t process unnecessary empty cells.
   • outputRow: Keeps track of where to place the next unique value in the output column.
2. Setting the Source Range:
   • We determine the last row of data in column A using Cells(Rows.Count, « A »).End(xlUp).Row. This finds the last cell with data in column A. We then set the sourceRange to include all cells from A1 to the last row.
3. Creating a Collection for Unique Values:
   • A new Collection is created. This is where we will store the unique values. The key used in the collection is the value itself (CStr(cell.Value)). The reason we use CStr(cell.Value) is that the collection uses the key to ensure no duplicates, and the key must be a unique string.
4. Looping Through the Source Range:
   • We loop through each cell in the sourceRange. If the cell has a value (i.e., it’s not empty), we attempt to add the value to the uniqueCollection.
   • The line On Error Resume Next ensures that if a duplicate value is encountered (i.e., an error occurs when trying to add a value that already exists in the collection), the code simply ignores it and moves on.
   • On Error GoTo 0 restores normal error handling once we’ve finished adding items to the collection.
5. Outputting Unique Values:
   • After extracting all unique values into the collection, we start outputting them to the specified outputRange (starting at B1).
   • We loop through the collection using For Each item In uniqueCollection and place each unique value into the output range, starting at the first row of column B.
   • The variable outputRow ensures that the unique values are written in consecutive rows.
6. Final Message:
   • Once the unique values are extracted and displayed, a message box pops up to notify the user that the task is complete.

How It Works in Practice:

• Suppose you have the following data in column A:
• A1: Apple
• A2: Banana
• A3: Apple
• A4: Orange
• A5: Banana
• A6: Grape
• After running the code, the unique values will be output in Column B:
• B1: Apple
• B2: Banana
• B3: Orange
• B4: Grape

Things to Consider:

• Handling Empty Cells: The code skips over empty cells by checking If cell.Value <> «  ». You can modify this behavior if you want to include empty values as well.
• Performance Considerations: The code is optimized for smaller datasets. However, for very large datasets (e.g., thousands of rows), the performance could degrade. In such cases, additional optimization might be required, such as using arrays to handle the data before processing.

Conclusion:

This VBA code provides an efficient way to extract unique values from a dataset in Excel. By leveraging VBA collections, we ensure that only distinct entries are extracted, making it a powerful tool for data cleaning or preparation.