Have Function Return Result as a Named Range

Eldrod

Board Regular
Joined
Mar 11, 2010
Messages
63
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?
VBA Code:
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!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
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?
 
Solution

Eldrod

Board Regular
Joined
Mar 11, 2010
Messages
63
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
As I said, I don't think that's possible. But you don't really need one, you can just use X2#
 

Eldrod

Board Regular
Joined
Mar 11, 2010
Messages
63
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,591
Messages
5,637,288
Members
416,963
Latest member
samfuge

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top