» Return unique items using VBA in Microsoft Excel

VBA macro tip contributed by Erlandsen Data Consulting offering Microsoft Excel Application development, template customization, support and training solutions

CATEGORY - Custom Functions , Printing in VBA VERSION - All Microsoft Excel Versions

The macro below will return all the unique items from a range to another range.
You can use the macro like this to copy all the unique items from A2:A100 to cell C1 and below:

FindUniqueValues Range("A2:A100"), Range("C1")

Sub FindUniqueValues(SourceRange As Range, TargetCell As Range)
    SourceRange.AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=TargetCell,  Unique:=True
End Sub