Feed results of UNIQUE function into array.

[Herakles]

Whilst using this code does the job, is there a way of avoiding having to create the two formula's on the worksheet to
populate the array directly. Thanks.

Public Function fncGetUnique(rngRange As Range) As Variant
Dim arr() As Variant

    Range("C2").Formula2 = "=SORT(UNIQUE(" & rngRange.Address & ",FALSE,FALSE))"
        
    Range("D2").Formula2 = "=CountA(C2#)"
    
    arr = Range("C2").Resize(Range("D2").Value, 1)
    
    fncGetUnique = arr

End Function

 

[FormR]

Hi, you could try like this:

Public Function fncGetUnique(rngRange As Range) As Variant
    fncGetUnique = Evaluate("SORT(UNIQUE(" & rngRange.Address & ",FALSE,FALSE))")
End Function

 

[Herakles]

Hi, you could try like this:

Public Function fncGetUnique(rngRange As Range) As Variant
    fncGetUnique = Evaluate("SORT(UNIQUE(" & rngRange.Address & ",FALSE,FALSE))")
End Function

Thanks FormR, I knew that there was a way.

Using Evaluate opens up many possibilities.

 

[Georgiboy]

Can also use Application:

Public Function fncGetUnique(rngRange As Range) As Variant
    With Application
        fncGetUnique = .Sort(.Unique(Range("A2:A11")))
    End With
End Function

 

[Herakles]

Can also use Application:

Public Function fncGetUnique(rngRange As Range) As Variant
    With Application
        fncGetUnique = .Sort(.Unique(Range("A2:A11")))
    End With
End Function

That works well too. Thanks.