The input range would not exceed 7 rows at a time, however, in the course of
creating my report, there would be many input ranges numbering several
hundred. I will use this to avoid calling the function twice.
Thanks, Mike F
"Chip Pearson" wrote in message
...
I think you can significantly simplify your code. First of all, you're
calling the DistinctValues function twice, which an lead to
performance problems if the input range is large.

Try code like

Sub AAA()
Dim InputRange As Range
Dim Vals As Variant
Set InputRange = Range("A1:A5")
Vals = DistinctValues(InputRange, True)
If IsArray(Vals) = True Then
Range("J1").Value = Join(Vals, ",")
End If
End Sub


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Sun, 22 Mar 2009 10:42:05 -0400, "Mike Fogleman"
wrote:

Thanks Chip, works perfect:

Public Function Join(Arr As Variant, Sep As String) As String
'joins ResultArray into single cell
Join = VBA.Join(Arr, Sep)
End Function

Sub Test2()
Dim InputRange As Range
Dim ResultArray As Variant
Dim Ndx As Long

Set InputRange = Range("InputValues")
ResultArray = DistinctValues(InputValues:=InputRange,
IgnoreCase:=True)
'Join(DistinctValues(A1:A5,TRUE),",")
If IsArray(ResultArray) = True Then
Range("J1").Value = Join(DistinctValues(InputRange, True), ", ")
'Debug.Print ResultArray(Ndx)
Else
If IsError(ResultArray) = True Then
Debug.Print "ERROR: " & CStr(ResultArray)
Else
Debug.Print "UNEXPECTED RESULT: " & CStr(ResultArray)
End If
End If
End Sub

Mike F
"Chip Pearson" wrote in message
. ..

Mike,

The site is back up and running.

Create a VBA function as follows:

Public Function Join(Arr As Variant, Sep As String) As String
Join = VBA.Join(Arr, Sep)
End Function

Then, in a worksheet cell, use the following formula:

=Join(DistinctValues(A1:A5,TRUE),",")

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Sun, 22 Mar 2009 09:17:01 -0400, "Mike Fogleman"
wrote:

BTW, Chip's site is throwing an error right now.

How can I get the array of distinct values results into a single cell
delimited with a comma?

InputRange
A2| E01AB
A3| E01AB
A4| E01CD
A5| A11

Output
AE15| E01AB,E01CD,A11

The input and output ranges will be determined with VB.
Mike F