Fantastic Chip! Both yours and Dave solution work perfectly. I can't thank
you all enough.

"Chip Pearson" wrote:


Tony,

Replace

Set RR = Range("A1:A10")

with

With Worksheets("Sheet1")
Set RR = Range(.Cells(1, "A"), _
.Cells(.Rows.Count, "A").End(xlUp))
End With

This will set RR to a range from row 1 to the last non-empty cell in
column A.

Replace

S = S & R.Text & ", "

with

If StrComp(R.Text, vbNullString) < 0 Then
S = S & R.Text & ", "
End If

This will skip empty cells.


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


On Wed, 18 Feb 2009 12:13:01 -0800, Tony S.
wrote:

Excellent Chip, as always.
Question: Since, the exact number of rows can always vary, is it possible to
set the range to a maximun number and not return extra spaces with commas for
blank cells? Like this?

Set RR = Range("A1:A999")

"Chip Pearson" wrote:

Try code like the following:

Sub AAA()
Dim S As String
Dim RR As Range
Dim R As Range

Set RR = Range("A1:A10")
RR.Sort key1:=Range("A1"), order1:=xlAscending
For Each R In RR.Cells
S = S & R.Text & ", "
Next R
S = Left(S, Len(S) - 2)
Range("C1").Value = S
End Sub

This sorts A1:A10 in ascending order and then writes all values as a
single text string into C1. Change ranges to meet your needs.

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


On Wed, 18 Feb 2009 10:06:06 -0800, Tony S.
wrote:

Is there a formula that can sort multiple cell in order lowest to highest,
then show the result in a single cell oriented horizontally?

I have this:
7
41
13
8
44
14
9
24
38
1
25
2
11
26
3
12
4
22
50
6
23
32
29
40
15
30
in diffenent rows. I'mtrying to get this...
1, 2, 3, 4, 6, 7, 8, 9, 11, 12, 13, 14, 15, 22, 23, 24, 25, 26, 29, 30, 32,
38, 40, 41, 44, 50
in one cell. Each number sepaated by a comma and space.
Thanks!