【VBA】Get unique list

You can get “unique list“！

Use the following Worksheet function:
　UNIQUE Function

You can use “UNIQUE Function” only in Office365.

VBA Code

As an example, get unique list “Birthplace”, Output to “immediate window”.

Option Explicit

Sub sampleProc()
    
    Dim targetSheet As Worksheet
    Dim startRange As Range
    Dim endRow As Double
    Dim endRange As Range
    Dim targetRange As Range
    Dim arrUniqueList As Variant
    Dim item As Variant
    
    'Get the target sheet with the list
    Set targetSheet = Worksheets("sample")

    'Get the start cell of the list
    Set startRange = targetSheet.Range("C3")
    'Get the last row of the list
    endRow = startRange.End(xlDown).Row
    'Get the last cell of the list
    Set endRange = targetSheet.Cells(endRow, startRange.Column)
    
    'Get the cells of the list
    Set targetRange = targetSheet.Range(startRange, endRange)
    
    'Get "unique list"
    arrUniqueList = WorksheetFunction.Unique(targetRange)
    
    For Each item In arrUniqueList
        'Output to "immediate window"
        Debug.Print item
    Next
    
End Sub

Result

You got “unique list“！

FYI No.1

Also, you can get “sorted list“！

For more information, see the following article:

【VBA】Get sorted list

You can get “sorted list“！

excel-vba-codes.com

FYI No.2

Also, you can get “filtered list“！

For more information, see the following article:

https://excel-vba-codes.com/how-to-get-filtered-list/

excel-vba-codes.com

FYI No.3

For more information about following, see the following official online manual:

●UNIQUE Function

UNIQUE function - Microsoft Support

Learn about the UNIQUE function, which returns a list of unique values in a list or range. UNIQUE is in a class of functions called dynamic arrays.

support.microsoft.com

●“Print method” of “Debug Object”

Print method (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com