Creating list of Unique values in offset cells

[GTO]

Just a comment: Not well researched, and I find nothing about it so far, but there seems to be a limitation of 65536 cells that can be passed to the UDF. So if you mean 50k cells total, I don't see an issue. On the other hand, I fooled around with a possible UDF and found that it falls over if above 2^16 cells are in the ranges.

I copied values from scowl.dic and plastered them in various columns. With the following UDF:

Public Function UNIQUEList2(ParamArray InputRanges() As Variant) As Variant
Dim IndividualRange  As Variant
Dim IndRange As Range
Dim Cell As Range
Dim Index As Long
Dim CellCount As Long
Dim Output As Variant
Dim AlternateOutput As Variant
Dim DIC As Object ' Scripting.Dictionary
  
  On Error GoTo errCheck
  
  Set DIC = CreateObject("Scripting.Dictionary")
  
  For Index = 0 To UBound(InputRanges)
    If Not TypeName(InputRanges(Index)) = "Range" Then
      Exit Function
    End If
  Next
  
  For Each IndividualRange In InputRanges
    Set IndRange = IndividualRange
    CellCount = CellCount + IndRange.Cells.Count
    For Each Cell In IndRange.Cells
      DIC.Item(Cell.Value) = Empty
    Next
  Next IndividualRange
  
  On Error Resume Next
  DIC.Remove (vbNullString)
  'On Error GoTo 0
  On Error GoTo errCheck
  
  Output = DIC.Keys
  ReDim Preserve Output(0 To (CellCount - 1))
  For Index = DIC.Count To CellCount - 1
    Output(Index) = vbNullString
  Next
  
  If Not CellCount > 65536 Then
    Output = Application.Transpose(Output)
    
    UNIQUEList2 = Output
  Else
    ReDim AlternateOutput(1 To CellCount, 1 To 1)
    For Index = 1 To UBound(Output, 1) + 1
      AlternateOutput(Index, 1) = Output(Index - 1)
    Next
    
    UNIQUEList2 = AlternateOutput
  End If
Exit Function
errCheck:
Stop
Resume Next
End Function

...array entered as: =UNIQUEList2(A2:A50001,C2:C15537) <---This works fine.

But if array entered as: =UNIQUEList2(A2:A50001,C2:C15538) <---This returns the #VALUE! error.

Of course it runs/returns fine in vba:

Sub test()
Dim a
a = UNIQUEList2(Range("A2:A50001"), Range("C2:C15538"))
Stop
End Sub

Hopefully someone will comment.

Mark

PS - I did find this, just in case the info is of help. https://support.microsoft.com/en-us/kb/170787