Have Function Return Result as a Named Range

[Eldrod]

I have a function that reads a column of cells, and returns a list of unique hits. That part is working well. What I also want it to do is to create a named range of the resulting list as well. It appears that Excel internally creates one. For example, when I run the function on cell X2 of a spreadsheet, Excel creates a system Named Range called x2#. My function is looking to provide the range of cells to search and the name of the named range I want to create from that result. Does anyone have a snippet that can help with this last step?

Function GetUniqueList(InData As Range, NRName As String) As Variant
'Purpose is to read an Array of input data and return an array of only unique items without duplicates
Dim InArray(), TempArray(), OutArray() As Variant
Dim OutRange As Range
Dim InRows, OutRows As Integer
Dim x, y, OutIdx As Integer
Dim MatchFound As Boolean
InArray = InData
InRows = UBound(InArray)
ReDim TempArray(InRows)
OutIdx = 0
'Process List
For x = 1 To InRows
    'Process each input item
    If InArray(x, 1) = "" Then
        'Skip
    Else
        MatchFound = False
        For y = 0 To OutIdx
            If InArray(x, 1) = TempArray(y) Then
                'Skip, already found
                MatchFound = True
                Exit For
            End If
        Next y
        If MatchFound Then
            'Skip
        Else
            TempArray(OutIdx) = InArray(x, 1)
            OutIdx = OutIdx + 1
        End If
    End If
Next x

OutIdx = OutIdx - 1
OutArray = TempArray
ReDim Preserve OutArray(OutIdx)

GetUniqueList = Application.Transpose(OutArray())
GetUniqueList.Name = NRName

Erase InArray, TempArray, OutArray
End Function

The GetUniqueList.Name is the final step I haven't been able to get to work. Any help is appreciated! Thanks!

 

[Fluff]

As far as I know, you cannot create a Defined Name inside a UDF.
That said, why are you using a UDF rather than just the UNIQUE function?

 

[Eldrod]

As far as I know, you cannot create a Defined Name inside a UDF.
That said, why are you using a UDF rather than just the UNIQUE function?

LOL, because I'm a code monkey and I start writing code before I look for existing functions! UNIQUE does the basic function, but I still want to try to figure out how to get that Named Range created if possible.

 

[Fluff]

As I said, I don't think that's possible. But you don't really need one, you can just use X2#

 

[Eldrod]

As I said, I don't think that's possible. But you don't really need one, you can just use X2#

Thanks! You were correct. It appears that a named range cannot be added from a function (it can be added by a subroutine). So I revised the code to, first, use the UNIQUE function (thanks!) and two, use a subroutine to create the named range from the results. Thanks again!

 

[Fluff]

You're welcome & thanks for the feedback.