Concatenating and transposing a row of numbers

Hi All

Hope someone can help.

I have a column of numbers going down the sheet in column A.

I need to concatenate all the numbers in each cell in the column and
separate each by a comma. This would then be displayed as a long list
all in cell B1.

I've been playing around for ages to do this - can someone put me out of
my misery?



Best Wishes

On Thu, 13 Jul 2006 20:51:02 +0100, Colin Hayes
<Colin@chayes.demon.co.uk> wrote:

>
>Hi All
>
>Hope someone can help.
>
>I have a column of numbers going down the sheet in column A.
>
>I need to concatenate all the numbers in each cell in the column and
>separate each by a comma. This would then be displayed as a long list
>all in cell B1.
>
>I've been playing around for ages to do this - can someone put me out of
>my misery?
>
>
>
>Best Wishes

With your numbers in say A1:A10, enter in
B1 =A1, and in
B2 =B1&","&A2

Now copy B2 down ro B10.

B10 now contains your concatenaated numbers. To convert them to a long
text string do Edit F9 and Enter.

Now copy B10 to B1 and delete B2:B10

HTH

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Try this macro:

Put this required cell;

=onelist(A1:A100) .

... set range as required


Function onelist(ByRef rng As range) as string
bStr = ""
For Each cell In rng
bStr = bStr & Trim(Str(cell.Value)) & ","
Next
onelist = Left(bStr, Len(bStr) - 1)
End Function

"Colin Hayes" wrote:

>
> Hi All
>
> Hope someone can help.
>
> I have a column of numbers going down the sheet in column A.
>
> I need to concatenate all the numbers in each cell in the column and
> separate each by a comma. This would then be displayed as a long list
> all in cell B1.
>
> I've been playing around for ages to do this - can someone put me out of
> my misery?
>
>
>
> Best Wishes
>
>

Toppers

One problem with this.

If a cell in the range is blank it returns a 0

So you get 1,2,3,0,0,0,7,8,9 if A4:A6 are blank.

This function ignores blanks.

Function ConCatRange(CellBlock As Range) As String
Dim cell As Range
Dim sbuf As String
For Each cell In CellBlock
If Len(cell.text) > 0 Then sbuf = sbuf & cell.text '' & ","
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function


Gord Dibben MS Excel MVP



On Thu, 13 Jul 2006 14:01:02 -0700, Toppers <Toppers@discussions.microsoft.com>
wrote:

>
>
>Try this macro:
>
>Put this required cell;
>
>=onelist(A1:A100) .
>
>.. set range as required
>
>
>Function onelist(ByRef rng As range) as string
>bStr = ""
> For Each cell In rng
> bStr = bStr & Trim(Str(cell.Value)) & ","
> Next
>onelist = Left(bStr, Len(bStr) - 1)
>End Function
>
>"Colin Hayes" wrote:
>
>>
>> Hi All
>>
>> Hope someone can help.
>>
>> I have a column of numbers going down the sheet in column A.
>>
>> I need to concatenate all the numbers in each cell in the column and
>> separate each by a comma. This would then be displayed as a long list
>> all in cell B1.
>>
>> I've been playing around for ages to do this - can someone put me out of
>> my misery?
>>
>>
>>
>> Best Wishes
>>
>>

In article <3107B997-6540-47BF-81D1-3FFA1F84CE7B@microsoft.com>, Toppers
<Toppers@discussions.microsoft.com> writes
>
>
>Try this macro:
>
>Put this required cell;
>
>=onelist(A1:A100) .
>
>.. set range as required
>
>
>Function onelist(ByRef rng As range) as string
>bStr = ""
> For Each cell In rng
> bStr = bStr & Trim(Str(cell.Value)) & ","
> Next
>onelist = Left(bStr, Len(bStr) - 1)
>End Function
>


Hi

OK Thanks for that.

I wasn't able to implement it , I'm afraid. I can run macros OK , but I
think you're expecting more programming skill than I possess!

(For example , when you say

>Put this required cell;
>
>=onelist(A1:A100) .
>
>.. set range as required

What do you mean? How do I do this?)



Best Wishes





>"Colin Hayes" wrote:
>
>>
>> Hi All
>>
>> Hope someone can help.
>>
>> I have a column of numbers going down the sheet in column A.
>>
>> I need to concatenate all the numbers in each cell in the column and
>> separate each by a comma. This would then be displayed as a long list
>> all in cell B1.
>>
>> I've been playing around for ages to do this - can someone put me out of
>> my misery?
>>
>>
>>
>> Best Wishes
>>

HI All

I'm pleased you were able to sort that out between yourselves...!

I'll do it manually ....

^_^

Best Wishes


Colin




In article <eqidb2hjmtvp363a8ncpunpgt5gprh2kd6@4ax.com>, Gord Dibben
<gorddibbATshawDOTca@?.?> writes
>Toppers
>
>One problem with this.
>
>If a cell in the range is blank it returns a 0
>
>So you get 1,2,3,0,0,0,7,8,9 if A4:A6 are blank.
>
>This function ignores blanks.
>
>Function ConCatRange(CellBlock As Range) As String
>Dim cell As Range
>Dim sbuf As String
> For Each cell In CellBlock
> If Len(cell.text) > 0 Then sbuf = sbuf & cell.text '' & ","
> Next
> ConCatRange = Left(sbuf, Len(sbuf) - 1)
>End Function
>
>
>Gord Dibben MS Excel MVP
>
>
>
>On Thu, 13 Jul 2006 14:01:02 -0700, Toppers <Toppers@discussions.microsoft.co
>m>
>wrote:
>
>>
>>
>>Try this macro:
>>
>>Put this required cell;
>>
>>=onelist(A1:A100) .
>>
>>.. set range as required
>>
>>
>>Function onelist(ByRef rng As range) as string
>>bStr = ""
>> For Each cell In rng
>> bStr = bStr & Trim(Str(cell.Value)) & ","
>> Next
>>onelist = Left(bStr, Len(bStr) - 1)
>>End Function
>>
>>"Colin Hayes" wrote:
>>
>>>
>>> Hi All
>>>
>>> Hope someone can help.
>>>
>>> I have a column of numbers going down the sheet in column A.
>>>
>>> I need to concatenate all the numbers in each cell in the column and
>>> separate each by a comma. This would then be displayed as a long list
>>> all in cell B1.
>>>
>>> I've been playing around for ages to do this - can someone put me out of
>>> my misery?
>>>
>>>
>>>
>>> Best Wishes
>>>
>>>
>

Your choice.

Have fun.

Gord

On Fri, 14 Jul 2006 03:47:40 +0100, Colin Hayes <Colin@chayes.demon.co.uk>
wrote:

>
>HI All
>
>I'm pleased you were able to sort that out between yourselves...!
>
>I'll do it manually ....
>
>^_^
>
>Best Wishes
>
>
>Colin
>
>
>
>
>In article <eqidb2hjmtvp363a8ncpunpgt5gprh2kd6@4ax.com>, Gord Dibben
><gorddibbATshawDOTca@?.?> writes
>>Toppers
>>
>>One problem with this.
>>
>>If a cell in the range is blank it returns a 0
>>
>>So you get 1,2,3,0,0,0,7,8,9 if A4:A6 are blank.
>>
>>This function ignores blanks.
>>
>>Function ConCatRange(CellBlock As Range) As String
>>Dim cell As Range
>>Dim sbuf As String
>> For Each cell In CellBlock
>> If Len(cell.text) > 0 Then sbuf = sbuf & cell.text '' & ","
>> Next
>> ConCatRange = Left(sbuf, Len(sbuf) - 1)
>>End Function
>>
>>
>>Gord Dibben MS Excel MVP
>>
>>
>>
>>On Thu, 13 Jul 2006 14:01:02 -0700, Toppers <Toppers@discussions.microsoft.co
>>m>
>>wrote:
>>
>>>
>>>
>>>Try this macro:
>>>
>>>Put this required cell;
>>>
>>>=onelist(A1:A100) .
>>>
>>>.. set range as required
>>>
>>>
>>>Function onelist(ByRef rng As range) as string
>>>bStr = ""
>>> For Each cell In rng
>>> bStr = bStr & Trim(Str(cell.Value)) & ","
>>> Next
>>>onelist = Left(bStr, Len(bStr) - 1)
>>>End Function
>>>
>>>"Colin Hayes" wrote:
>>>
>>>>
>>>> Hi All
>>>>
>>>> Hope someone can help.
>>>>
>>>> I have a column of numbers going down the sheet in column A.
>>>>
>>>> I need to concatenate all the numbers in each cell in the column and
>>>> separate each by a comma. This would then be displayed as a long list
>>>> all in cell B1.
>>>>
>>>> I've been playing around for ages to do this - can someone put me out of
>>>> my misery?
>>>>
>>>>
>>>>
>>>> Best Wishes
>>>>
>>>>
>>

Gord Dibben MS Excel MVP

Thanks Gord ... I appreciate the feedback!!!

"Gord Dibben" wrote:

> Toppers
>
> One problem with this.
>
> If a cell in the range is blank it returns a 0
>
> So you get 1,2,3,0,0,0,7,8,9 if A4:A6 are blank.
>
> This function ignores blanks.
>
> Function ConCatRange(CellBlock As Range) As String
> Dim cell As Range
> Dim sbuf As String
> For Each cell In CellBlock
> If Len(cell.text) > 0 Then sbuf = sbuf & cell.text '' & ","
> Next
> ConCatRange = Left(sbuf, Len(sbuf) - 1)
> End Function
>
>
> Gord Dibben MS Excel MVP
>
>
>
> On Thu, 13 Jul 2006 14:01:02 -0700, Toppers <Toppers@discussions.microsoft.com>
> wrote:
>
> >
> >
> >Try this macro:
> >
> >Put this required cell;
> >
> >=onelist(A1:A100) .
> >
> >.. set range as required
> >
> >
> >Function onelist(ByRef rng As range) as string
> >bStr = ""
> > For Each cell In rng
> > bStr = bStr & Trim(Str(cell.Value)) & ","
> > Next
> >onelist = Left(bStr, Len(bStr) - 1)
> >End Function
> >
> >"Colin Hayes" wrote:
> >
> >>
> >> Hi All
> >>
> >> Hope someone can help.
> >>
> >> I have a column of numbers going down the sheet in column A.
> >>
> >> I need to concatenate all the numbers in each cell in the column and
> >> separate each by a comma. This would then be displayed as a long list
> >> all in cell B1.
> >>
> >> I've been playing around for ages to do this - can someone put me out of
> >> my misery?
> >>
> >>
> >>
> >> Best Wishes
> >>
> >>
>
>

In article <5o5eb29l7bki7a0778mbe6bnonca5rbd1f@4ax.com>, Gord Dibben
<gorddibbATshawDOTca@?.?> writes
>Your choice.
>
>Have fun.
>
>Gord

HI Gord

No , not my choice at all. Quite the opposite in fact. I spent some time
trying to implement the code , and wasn't able to.

I'm grateful for any advice given of course , but we're not all experts.
MVPs have a role as instructor , don't they?


Best Wishes


Colin


>
>On Fri, 14 Jul 2006 03:47:40 +0100, Colin Hayes <Colin@chayes.demon.co.uk>
>wrote:
>
>>
>>HI All
>>
>>I'm pleased you were able to sort that out between yourselves...!
>>
>>I'll do it manually ....
>>
>>^_^
>>
>>Best Wishes
>>
>>
>>Colin
>>
>>
>>
>>
>>In article <eqidb2hjmtvp363a8ncpunpgt5gprh2kd6@4ax.com>, Gord Dibben
>><gorddibbATshawDOTca@?.?> writes
>>>Toppers
>>>
>>>One problem with this.
>>>
>>>If a cell in the range is blank it returns a 0
>>>
>>>So you get 1,2,3,0,0,0,7,8,9 if A4:A6 are blank.
>>>
>>>This function ignores blanks.
>>>
>>>Function ConCatRange(CellBlock As Range) As String
>>>Dim cell As Range
>>>Dim sbuf As String
>>> For Each cell In CellBlock
>>> If Len(cell.text) > 0 Then sbuf = sbuf & cell.text '' & ","
>>> Next
>>> ConCatRange = Left(sbuf, Len(sbuf) - 1)
>>>End Function
>>>
>>>
>>>Gord Dibben MS Excel MVP
>>>
>>>
>>>
>>>On Thu, 13 Jul 2006 14:01:02 -0700, Toppers <Toppers@discussions.microsof
>t.
>co
>>>m>
>>>wrote:
>>>
>>>>
>>>>
>>>>Try this macro:
>>>>
>>>>Put this required cell;
>>>>
>>>>=onelist(A1:A100) .
>>>>
>>>>.. set range as required
>>>>
>>>>
>>>>Function onelist(ByRef rng As range) as string
>>>>bStr = ""
>>>> For Each cell In rng
>>>> bStr = bStr & Trim(Str(cell.Value)) & ","
>>>> Next
>>>>onelist = Left(bStr, Len(bStr) - 1)
>>>>End Function
>>>>
>>>>"Colin Hayes" wrote:
>>>>
>>>>>
>>>>> Hi All
>>>>>
>>>>> Hope someone can help.
>>>>>
>>>>> I have a column of numbers going down the sheet in column A.
>>>>>
>>>>> I need to concatenate all the numbers in each cell in the column and
>>>>> separate each by a comma. This would then be displayed as a long list
>>>>> all in cell B1.
>>>>>
>>>>> I've been playing around for ages to do this - can someone put me out of
>>>>> my misery?
>>>>>
>>>>>
>>>>>
>>>>> Best Wishes
>>>>>
>>>>>
>>>
>
>Gord Dibben MS Excel MVP

Colin

Apologies for the misunderstanding.

I saw a post from you asking Toppers for instructions on how to implement the
Function and my assumer got stuck.

If not familiar with macros and VBA, visit David McRitchie's website on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the ConCatRange function code in there.

Save the workbook and hit ALT + Q to return to your workbook.

Enter the formula =ConCatRange(A1:A20) in B1


Gord

On Fri, 14 Jul 2006 14:24:40 +0100, Colin Hayes <Colin@chayes.demon.co.uk>
wrote:

>In article <5o5eb29l7bki7a0778mbe6bnonca5rbd1f@4ax.com>, Gord Dibben
><gorddibbATshawDOTca@?.?> writes
>>Your choice.
>>
>>Have fun.
>>
>>Gord
>
>HI Gord
>
>No , not my choice at all. Quite the opposite in fact. I spent some time
>trying to implement the code , and wasn't able to.
>
>I'm grateful for any advice given of course , but we're not all experts.
>MVPs have a role as instructor , don't they?
>
>
>Best Wishes
>
>
>Colin
>
>
>>
>>On Fri, 14 Jul 2006 03:47:40 +0100, Colin Hayes <Colin@chayes.demon.co.uk>
>>wrote:
>>
>>>
>>>HI All
>>>
>>>I'm pleased you were able to sort that out between yourselves...!
>>>
>>>I'll do it manually ....
>>>
>>>^_^
>>>
>>>Best Wishes
>>>
>>>
>>>Colin
>>>
>>>
>>>
>>>
>>>In article <eqidb2hjmtvp363a8ncpunpgt5gprh2kd6@4ax.com>, Gord Dibben
>>><gorddibbATshawDOTca@?.?> writes
>>>>Toppers
>>>>
>>>>One problem with this.
>>>>
>>>>If a cell in the range is blank it returns a 0
>>>>
>>>>So you get 1,2,3,0,0,0,7,8,9 if A4:A6 are blank.
>>>>
>>>>This function ignores blanks.
>>>>
>>>>Function ConCatRange(CellBlock As Range) As String
>>>>Dim cell As Range
>>>>Dim sbuf As String
>>>> For Each cell In CellBlock
>>>> If Len(cell.text) > 0 Then sbuf = sbuf & cell.text '' & ","
>>>> Next
>>>> ConCatRange = Left(sbuf, Len(sbuf) - 1)
>>>>End Function
>>>>
>>>>
>>>>Gord Dibben MS Excel MVP
>>>>
>>>>
>>>>
>>>>On Thu, 13 Jul 2006 14:01:02 -0700, Toppers <Toppers@discussions.microsof
>>t.
>>co
>>>>m>
>>>>wrote:
>>>>
>>>>>
>>>>>
>>>>>Try this macro:
>>>>>
>>>>>Put this required cell;
>>>>>
>>>>>=onelist(A1:A100) .
>>>>>
>>>>>.. set range as required
>>>>>
>>>>>
>>>>>Function onelist(ByRef rng As range) as string
>>>>>bStr = ""
>>>>> For Each cell In rng
>>>>> bStr = bStr & Trim(Str(cell.Value)) & ","
>>>>> Next
>>>>>onelist = Left(bStr, Len(bStr) - 1)
>>>>>End Function
>>>>>
>>>>>"Colin Hayes" wrote:
>>>>>
>>>>>>
>>>>>> Hi All
>>>>>>
>>>>>> Hope someone can help.
>>>>>>
>>>>>> I have a column of numbers going down the sheet in column A.
>>>>>>
>>>>>> I need to concatenate all the numbers in each cell in the column and
>>>>>> separate each by a comma. This would then be displayed as a long list
>>>>>> all in cell B1.
>>>>>>
>>>>>> I've been playing around for ages to do this - can someone put me out of
>>>>>> my misery?
>>>>>>
>>>>>>
>>>>>>
>>>>>> Best Wishes
>>>>>>
>>>>>>
>>>>
>>
>>Gord Dibben MS Excel MVP

Gord Dibben MS Excel MVP

Hi Gord

OK Thanks for that. Very helpful.



Best Wishes


Colin





In article <e8kfb2p51fl0hu7676kg80tbn9fc493vck@4ax.com>, Gord Dibben
<gorddibbATshawDOTca@?.?> writes
>Colin
>
>Apologies for the misunderstanding.
>
>I saw a post from you asking Toppers for instructions on how to implement the
>Function and my assumer got stuck.
>
>If not familiar with macros and VBA, visit David McRitchie's website on
>"getting started".
>
>http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
>In the meantime..........
>
>To create a General Module, hit ALT + F11 to open the Visual Basic Editor.
>
>Hit CRTL + R to open Project Explorer.
>
>Find your workbook/project and select it.
>
>Right-click and Insert>Module. Paste the ConCatRange function code in there.
>
>Save the workbook and hit ALT + Q to return to your workbook.
>
>Enter the formula =ConCatRange(A1:A20) in B1
>
>
>Gord
>
>On Fri, 14 Jul 2006 14:24:40 +0100, Colin Hayes <Colin@chayes.demon.co.uk>
>wrote:
>
>>In article <5o5eb29l7bki7a0778mbe6bnonca5rbd1f@4ax.com>, Gord Dibben
>><gorddibbATshawDOTca@?.?> writes
>>>Your choice.
>>>
>>>Have fun.
>>>
>>>Gord
>>
>>HI Gord
>>
>>No , not my choice at all. Quite the opposite in fact. I spent some time
>>trying to implement the code , and wasn't able to.
>>
>>I'm grateful for any advice given of course , but we're not all experts.
>>MVPs have a role as instructor , don't they?
>>
>>
>>Best Wishes
>>
>>
>>Colin
>>
>>
>>>
>>>On Fri, 14 Jul 2006 03:47:40 +0100, Colin Hayes
><Colin@chayes.demon.co.uk>
>>>wrote:
>>>
>>>>
>>>>HI All
>>>>
>>>>I'm pleased you were able to sort that out between yourselves...!
>>>>
>>>>I'll do it manually ....
>>>>
>>>>^_^
>>>>
>>>>Best Wishes
>>>>
>>>>
>>>>Colin
>>>>
>>>>
>>>>
>>>>
>>>>In article <eqidb2hjmtvp363a8ncpunpgt5gprh2kd6@4ax.com>, Gord Dibben
>>>><gorddibbATshawDOTca@?.?> writes
>>>>>Toppers
>>>>>
>>>>>One problem with this.
>>>>>
>>>>>If a cell in the range is blank it returns a 0
>>>>>
>>>>>So you get 1,2,3,0,0,0,7,8,9 if A4:A6 are blank.
>>>>>
>>>>>This function ignores blanks.
>>>>>
>>>>>Function ConCatRange(CellBlock As Range) As String
>>>>>Dim cell As Range
>>>>>Dim sbuf As String
>>>>> For Each cell In CellBlock
>>>>> If Len(cell.text) > 0 Then sbuf = sbuf & cell.text '' & ","
>>>>> Next
>>>>> ConCatRange = Left(sbuf, Len(sbuf) - 1)
>>>>>End Function
>>>>>
>>>>>
>>>>>Gord Dibben MS Excel MVP
>>>>>
>>>>>
>>>>>
>>>>>On Thu, 13 Jul 2006 14:01:02 -0700, Toppers <Toppers@discussions.micros
>of
>>>t.
>>>co
>>>>>m>
>>>>>wrote:
>>>>>
>>>>>>
>>>>>>
>>>>>>Try this macro:
>>>>>>
>>>>>>Put this required cell;
>>>>>>
>>>>>>=onelist(A1:A100) .
>>>>>>
>>>>>>.. set range as required
>>>>>>
>>>>>>
>>>>>>Function onelist(ByRef rng As range) as string
>>>>>>bStr = ""
>>>>>> For Each cell In rng
>>>>>> bStr = bStr & Trim(Str(cell.Value)) & ","
>>>>>> Next
>>>>>>onelist = Left(bStr, Len(bStr) - 1)
>>>>>>End Function
>>>>>>
>>>>>>"Colin Hayes" wrote:
>>>>>>
>>>>>>>
>>>>>>> Hi All
>>>>>>>
>>>>>>> Hope someone can help.
>>>>>>>
>>>>>>> I have a column of numbers going down the sheet in column A.
>>>>>>>
>>>>>>> I need to concatenate all the numbers in each cell in the column and
>>>>>>> separate each by a comma. This would then be displayed as a long list
>>>>>>> all in cell B1.
>>>>>>>
>>>>>>> I've been playing around for ages to do this - can someone put me out of
>>>>>>> my misery?
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Best Wishes
>>>>>>>
>>>>>>>
>>>>>
>>>
>>>Gord Dibben MS Excel MVP
>
>Gord Dibben MS Excel MVP

HI Gord


OK I've entered and saved this function into the code in my workbook :

Function ConCatRange(CellBlock As Range) As String
Dim cell As Range
Dim sbuf As String
For Each cell In CellBlock
If Len(cell.text) > 0 Then sbuf = sbuf & cell.text '' & ","
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function



and entered the formula

=ConCatRange(A1:A20) in B1


I have my numbers down the sheet in column A from A1 to A20.


I'm getting the error #NAME? In B1. I've checked all the coding and
formula and all does look well. Any ideas where it might be going wrong?


The VBA editor does seem to be giving an error in this phrase :

If Len(cell.text) > 0 Then sbuf = sbuf & cell.text '' & ","

It goes green after the word 'text'. If i change any part of this I get
red errors....


Thanks again.



Best Wishes


Colin








In article <IXX50IAMsEuEFwau@chayes.demon.co.uk>, Colin Hayes
<Colin@chayes.demon.co.uk> writes
>
>
>Hi Gord
>
>OK Thanks for that. Very helpful.
>
>
>
>Best Wishes
>
>
>Colin
>
>
>
>
>
>In article <e8kfb2p51fl0hu7676kg80tbn9fc493vck@4ax.com>, Gord Dibben
><gorddibbATshawDOTca@?.?> writes
>>Colin
>>
>>Apologies for the misunderstanding.
>>
>>I saw a post from you asking Toppers for instructions on how to implement the
>>Function and my assumer got stuck.
>>
>>If not familiar with macros and VBA, visit David McRitchie's website on
>>"getting started".
>>
>>http://www.mvps.org/dmcritchie/excel/getstarted.htm
>>
>>In the meantime..........
>>
>>To create a General Module, hit ALT + F11 to open the Visual Basic Editor.
>>
>>Hit CRTL + R to open Project Explorer.
>>
>>Find your workbook/project and select it.
>>
>>Right-click and Insert>Module. Paste the ConCatRange function code in there.
>>
>>Save the workbook and hit ALT + Q to return to your workbook.
>>
>>Enter the formula =ConCatRange(A1:A20) in B1
>>
>>
>>Gord
>>
>>On Fri, 14 Jul 2006 14:24:40 +0100, Colin Hayes
><Colin@chayes.demon.co.uk>
>>wrote:
>>
>>>In article <5o5eb29l7bki7a0778mbe6bnonca5rbd1f@4ax.com>, Gord Dibben
>>><gorddibbATshawDOTca@?.?> writes
>>>>Your choice.
>>>>
>>>>Have fun.
>>>>
>>>>Gord
>>>
>>>HI Gord
>>>
>>>No , not my choice at all. Quite the opposite in fact. I spent some time
>>>trying to implement the code , and wasn't able to.
>>>
>>>I'm grateful for any advice given of course , but we're not all experts.
>>>MVPs have a role as instructor , don't they?
>>>
>>>
>>>Best Wishes
>>>
>>>
>>>Colin
>>>
>>>
>>>>
>>>>On Fri, 14 Jul 2006 03:47:40 +0100, Colin Hayes
>><Colin@chayes.demon.co.uk>
>>>>wrote:
>>>>
>>>>>
>>>>>HI All
>>>>>
>>>>>I'm pleased you were able to sort that out between yourselves...!
>>>>>
>>>>>I'll do it manually ....
>>>>>
>>>>>^_^
>>>>>
>>>>>Best Wishes
>>>>>
>>>>>
>>>>>Colin
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>In article <eqidb2hjmtvp363a8ncpunpgt5gprh2kd6@4ax.com>, Gord Dibben
>>>>><gorddibbATshawDOTca@?.?> writes
>>>>>>Toppers
>>>>>>
>>>>>>One problem with this.
>>>>>>
>>>>>>If a cell in the range is blank it returns a 0
>>>>>>
>>>>>>So you get 1,2,3,0,0,0,7,8,9 if A4:A6 are blank.
>>>>>>
>>>>>>This function ignores blanks.
>>>>>>
>>>>>>Function ConCatRange(CellBlock As Range) As String
>>>>>>Dim cell As Range
>>>>>>Dim sbuf As String
>>>>>> For Each cell In CellBlock
>>>>>> If Len(cell.text) > 0 Then sbuf = sbuf & cell.text '' & ","
>>>>>> Next
>>>>>> ConCatRange = Left(sbuf, Len(sbuf) - 1)
>>>>>>End Function
>>>>>>
>>>>>>
>>>>>>Gord Dibben MS Excel MVP
>>>>>>
>>>>>>
>>>>>>
>>>>>>On Thu, 13 Jul 2006 14:01:02 -0700, Toppers
><Toppers@discussions.micros
>>of
>>>>t.
>>>>co
>>>>>>m>
>>>>>>wrote:
>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>Try this macro:
>>>>>>>
>>>>>>>Put this required cell;
>>>>>>>
>>>>>>>=onelist(A1:A100) .
>>>>>>>
>>>>>>>.. set range as required
>>>>>>>
>>>>>>>
>>>>>>>Function onelist(ByRef rng As range) as string
>>>>>>>bStr = ""
>>>>>>> For Each cell In rng
>>>>>>> bStr = bStr & Trim(Str(cell.Value)) & ","
>>>>>>> Next
>>>>>>>onelist = Left(bStr, Len(bStr) - 1)
>>>>>>>End Function
>>>>>>>
>>>>>>>"Colin Hayes" wrote:
>>>>>>>
>>>>>>>>
>>>>>>>> Hi All
>>>>>>>>
>>>>>>>> Hope someone can help.
>>>>>>>>
>>>>>>>> I have a column of numbers going down the sheet in column A.
>>>>>>>>
>>>>>>>> I need to concatenate all the numbers in each cell in the column and
>>>>>>>> separate each by a comma. This would then be displayed as a long list
>>>>>>>> all in cell B1.
>>>>>>>>
>>>>>>>> I've been playing around for ages to do this - can someone put me out
>of
>>>>>>>> my misery?
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> Best Wishes
>>>>>>>>
>>>>>>>>
>>>>>>
>>>>
>>>>Gord Dibben MS Excel MVP
>>
>>Gord Dibben MS Excel MVP
>

Colin

The green part in the line is caused by the two single quotes I put in telling
Excel to ignore that part.

Another poster did not want the commas so I remmed that part out and sent you
the same code by mistake.

You don't want that part ignored, so remove them to this.

If Len(cell.text) > 0 Then sbuf = sbuf & cell.text & ","

That is not the cause of the #NAME? error however.

That error usually comes from Excel not recognizing the function name.

Did you Copy/paste to a General Module in the same workbook?

If you click on the Fx button and select User Defined from the list, does
ConCatRange show up?


Gord

On Sat, 15 Jul 2006 17:45:24 +0100, Colin Hayes <Colin@chayes.demon.co.uk>
wrote:

>
>HI Gord
>
>
>OK I've entered and saved this function into the code in my workbook :
>
>Function ConCatRange(CellBlock As Range) As String
>Dim cell As Range
>Dim sbuf As String
> For Each cell In CellBlock
> If Len(cell.text) > 0 Then sbuf = sbuf & cell.text '' & ","
> Next
> ConCatRange = Left(sbuf, Len(sbuf) - 1)
>End Function
>
>
>
>and entered the formula
>
>=ConCatRange(A1:A20) in B1
>
>
>I have my numbers down the sheet in column A from A1 to A20.
>
>
>I'm getting the error #NAME? In B1. I've checked all the coding and
>formula and all does look well. Any ideas where it might be going wrong?
>
>
>The VBA editor does seem to be giving an error in this phrase :
>
> If Len(cell.text) > 0 Then sbuf = sbuf & cell.text '' & ","
>
>It goes green after the word 'text'. If i change any part of this I get
>red errors....
>
>
>Thanks again.
>
>
>
>Best Wishes
>
>
>Colin
>
>
>
>
>
>
>
>
>In article <IXX50IAMsEuEFwau@chayes.demon.co.uk>, Colin Hayes
><Colin@chayes.demon.co.uk> writes
>>
>>
>>Hi Gord
>>
>>OK Thanks for that. Very helpful.
>>
>>
>>
>>Best Wishes
>>
>>
>>Colin
>>
>>
>>
>>
>>
>>In article <e8kfb2p51fl0hu7676kg80tbn9fc493vck@4ax.com>, Gord Dibben
>><gorddibbATshawDOTca@?.?> writes
>>>Colin
>>>
>>>Apologies for the misunderstanding.
>>>
>>>I saw a post from you asking Toppers for instructions on how to implement the
>>>Function and my assumer got stuck.
>>>
>>>If not familiar with macros and VBA, visit David McRitchie's website on
>>>"getting started".
>>>
>>>http://www.mvps.org/dmcritchie/excel/getstarted.htm
>>>
>>>In the meantime..........
>>>
>>>To create a General Module, hit ALT + F11 to open the Visual Basic Editor.
>>>
>>>Hit CRTL + R to open Project Explorer.
>>>
>>>Find your workbook/project and select it.
>>>
>>>Right-click and Insert>Module. Paste the ConCatRange function code in there.
>>>
>>>Save the workbook and hit ALT + Q to return to your workbook.
>>>
>>>Enter the formula =ConCatRange(A1:A20) in B1
>>>
>>>
>>>Gord
>>>
>>>On Fri, 14 Jul 2006 14:24:40 +0100, Colin Hayes
>><Colin@chayes.demon.co.uk>
>>>wrote:
>>>
>>>>In article <5o5eb29l7bki7a0778mbe6bnonca5rbd1f@4ax.com>, Gord Dibben
>>>><gorddibbATshawDOTca@?.?> writes
>>>>>Your choice.
>>>>>
>>>>>Have fun.
>>>>>
>>>>>Gord
>>>>
>>>>HI Gord
>>>>
>>>>No , not my choice at all. Quite the opposite in fact. I spent some time
>>>>trying to implement the code , and wasn't able to.
>>>>
>>>>I'm grateful for any advice given of course , but we're not all experts.
>>>>MVPs have a role as instructor , don't they?
>>>>
>>>>
>>>>Best Wishes
>>>>
>>>>
>>>>Colin
>>>>
>>>>
>>>>>
>>>>>On Fri, 14 Jul 2006 03:47:40 +0100, Colin Hayes
>>><Colin@chayes.demon.co.uk>
>>>>>wrote:
>>>>>
>>>>>>
>>>>>>HI All
>>>>>>
>>>>>>I'm pleased you were able to sort that out between yourselves...!
>>>>>>
>>>>>>I'll do it manually ....
>>>>>>
>>>>>>^_^
>>>>>>
>>>>>>Best Wishes
>>>>>>
>>>>>>
>>>>>>Colin
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>In article <eqidb2hjmtvp363a8ncpunpgt5gprh2kd6@4ax.com>, Gord Dibben
>>>>>><gorddibbATshawDOTca@?.?> writes
>>>>>>>Toppers
>>>>>>>
>>>>>>>One problem with this.
>>>>>>>
>>>>>>>If a cell in the range is blank it returns a 0
>>>>>>>
>>>>>>>So you get 1,2,3,0,0,0,7,8,9 if A4:A6 are blank.
>>>>>>>
>>>>>>>This function ignores blanks.
>>>>>>>
>>>>>>>Function ConCatRange(CellBlock As Range) As String
>>>>>>>Dim cell As Range
>>>>>>>Dim sbuf As String
>>>>>>> For Each cell In CellBlock
>>>>>>> If Len(cell.text) > 0 Then sbuf = sbuf & cell.text '' & ","
>>>>>>> Next
>>>>>>> ConCatRange = Left(sbuf, Len(sbuf) - 1)
>>>>>>>End Function
>>>>>>>
>>>>>>>
>>>>>>>Gord Dibben MS Excel MVP
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>On Thu, 13 Jul 2006 14:01:02 -0700, Toppers
>><Toppers@discussions.micros
>>>of
>>>>>t.
>>>>>co
>>>>>>>m>
>>>>>>>wrote:
>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>Try this macro:
>>>>>>>>
>>>>>>>>Put this required cell;
>>>>>>>>
>>>>>>>>=onelist(A1:A100) .
>>>>>>>>
>>>>>>>>.. set range as required
>>>>>>>>
>>>>>>>>
>>>>>>>>Function onelist(ByRef rng As range) as string
>>>>>>>>bStr = ""
>>>>>>>> For Each cell In rng
>>>>>>>> bStr = bStr & Trim(Str(cell.Value)) & ","
>>>>>>>> Next
>>>>>>>>onelist = Left(bStr, Len(bStr) - 1)
>>>>>>>>End Function
>>>>>>>>
>>>>>>>>"Colin Hayes" wrote:
>>>>>>>>
>>>>>>>>>
>>>>>>>>> Hi All
>>>>>>>>>
>>>>>>>>> Hope someone can help.
>>>>>>>>>
>>>>>>>>> I have a column of numbers going down the sheet in column A.
>>>>>>>>>
>>>>>>>>> I need to concatenate all the numbers in each cell in the column and
>>>>>>>>> separate each by a comma. This would then be displayed as a long list
>>>>>>>>> all in cell B1.
>>>>>>>>>
>>>>>>>>> I've been playing around for ages to do this - can someone put me out
>>of
>>>>>>>>> my misery?
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> Best Wishes
>>>>>>>>>
>>>>>>>>>
>>>>>>>
>>>>>
>>>>>Gord Dibben MS Excel MVP
>>>
>>>Gord Dibben MS Excel MVP
>>

Hi Gord

Ok thanks for getting back.

I went through the process again , and this time it worked fine. i must
just have made an error when I first did it. Anyway all is well now and
working perfectly. Thanks!

BTW Once saved as a module in a project , does the coding that we've
added become embedded in the sheet itself? If it I ran it on another
machine , would it run independently? Or would i need to put the code in
again on another PC?



Best Wishes


Colin







In article <6hbib2d4cncm2tpgn6k58uq05pn4bl1m93@4ax.com>, Gord Dibben
<gorddibbATshawDOTca@?.?> writes
>Colin
>
>The green part in the line is caused by the two single quotes I put in telling
>Excel to ignore that part.
>
>Another poster did not want the commas so I remmed that part out and sent you
>the same code by mistake.
>
>You don't want that part ignored, so remove them to this.
>
> If Len(cell.text) > 0 Then sbuf = sbuf & cell.text & ","
>
>That is not the cause of the #NAME? error however.
>
>That error usually comes from Excel not recognizing the function name.
>
>Did you Copy/paste to a General Module in the same workbook?
>
>If you click on the Fx button and select User Defined from the list, does
>ConCatRange show up?
>
>
>Gord
>
>On Sat, 15 Jul 2006 17:45:24 +0100, Colin Hayes <Colin@chayes.demon.co.uk>
>wrote:
>
>>
>>HI Gord
>>
>>
>>OK I've entered and saved this function into the code in my workbook :
>>
>>Function ConCatRange(CellBlock As Range) As String
>>Dim cell As Range
>>Dim sbuf As String
>> For Each cell In CellBlock
>> If Len(cell.text) > 0 Then sbuf = sbuf & cell.text '' & ","
>> Next
>> ConCatRange = Left(sbuf, Len(sbuf) - 1)
>>End Function
>>
>>
>>
>>and entered the formula
>>
>>=ConCatRange(A1:A20) in B1
>>
>>
>>I have my numbers down the sheet in column A from A1 to A20.
>>
>>
>>I'm getting the error #NAME? In B1. I've checked all the coding and
>>formula and all does look well. Any ideas where it might be going wrong?
>>
>>
>>The VBA editor does seem to be giving an error in this phrase :
>>
>> If Len(cell.text) > 0 Then sbuf = sbuf & cell.text '' & ","
>>
>>It goes green after the word 'text'. If i change any part of this I get
>>red errors....
>>
>>
>>Thanks again.
>>
>>
>>
>>Best Wishes
>>
>>
>>Colin
>>
>>
>>
>>
>>
>>
>>
>>
>>In article <IXX50IAMsEuEFwau@chayes.demon.co.uk>, Colin Hayes
>><Colin@chayes.demon.co.uk> writes
>>>
>>>
>>>Hi Gord
>>>
>>>OK Thanks for that. Very helpful.
>>>
>>>
>>>
>>>Best Wishes
>>>
>>>
>>>Colin
>>>
>>>
>>>
>>>
>>>
>>>In article <e8kfb2p51fl0hu7676kg80tbn9fc493vck@4ax.com>, Gord Dibben
>>><gorddibbATshawDOTca@?.?> writes
>>>>Colin
>>>>
>>>>Apologies for the misunderstanding.
>>>>
>>>>I saw a post from you asking Toppers for instructions on how to implement the
>>>>Function and my assumer got stuck.
>>>>
>>>>If not familiar with macros and VBA, visit David McRitchie's website on
>>>>"getting started".
>>>>
>>>>http://www.mvps.org/dmcritchie/excel/getstarted.htm
>>>>
>>>>In the meantime..........
>>>>
>>>>To create a General Module, hit ALT + F11 to open the Visual Basic Editor.
>>>>
>>>>Hit CRTL + R to open Project Explorer.
>>>>
>>>>Find your workbook/project and select it.
>>>>
>>>>Right-click and Insert>Module. Paste the ConCatRange function code in there.
>>>>
>>>>Save the workbook and hit ALT + Q to return to your workbook.
>>>>
>>>>Enter the formula =ConCatRange(A1:A20) in B1
>>>>
>>>>
>>>>Gord
>>>>
>>>>On Fri, 14 Jul 2006 14:24:40 +0100, Colin Hayes
>>><Colin@chayes.demon.co.uk>
>>>>wrote:
>>>>
>>>>>In article <5o5eb29l7bki7a0778mbe6bnonca5rbd1f@4ax.com>, Gord Dibben
>>>>><gorddibbATshawDOTca@?.?> writes
>>>>>>Your choice.
>>>>>>
>>>>>>Have fun.
>>>>>>
>>>>>>Gord
>>>>>
>>>>>HI Gord
>>>>>
>>>>>No , not my choice at all. Quite the opposite in fact. I spent some time
>>>>>trying to implement the code , and wasn't able to.
>>>>>
>>>>>I'm grateful for any advice given of course , but we're not all experts.
>>>>>MVPs have a role as instructor , don't they?
>>>>>
>>>>>
>>>>>Best Wishes
>>>>>
>>>>>
>>>>>Colin
>>>>>
>>>>>
>>>>>>
>>>>>>On Fri, 14 Jul 2006 03:47:40 +0100, Colin Hayes
>>>><Colin@chayes.demon.co.uk>
>>>>>>wrote:
>>>>>>
>>>>>>>
>>>>>>>HI All
>>>>>>>
>>>>>>>I'm pleased you were able to sort that out between yourselves...!
>>>>>>>
>>>>>>>I'll do it manually ....
>>>>>>>
>>>>>>>^_^
>>>>>>>
>>>>>>>Best Wishes
>>>>>>>
>>>>>>>
>>>>>>>Colin
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>In article <eqidb2hjmtvp363a8ncpunpgt5gprh2kd6@4ax.com>, Gord
>Dibben
>>>>>>><gorddibbATshawDOTca@?.?> writes
>>>>>>>>Toppers
>>>>>>>>
>>>>>>>>One problem with this.
>>>>>>>>
>>>>>>>>If a cell in the range is blank it returns a 0
>>>>>>>>
>>>>>>>>So you get 1,2,3,0,0,0,7,8,9 if A4:A6 are blank.
>>>>>>>>
>>>>>>>>This function ignores blanks.
>>>>>>>>
>>>>>>>>Function ConCatRange(CellBlock As Range) As String
>>>>>>>>Dim cell As Range
>>>>>>>>Dim sbuf As String
>>>>>>>> For Each cell In CellBlock
>>>>>>>> If Len(cell.text) > 0 Then sbuf = sbuf & cell.text '' & ","
>>>>>>>> Next
>>>>>>>> ConCatRange = Left(sbuf, Len(sbuf) - 1)
>>>>>>>>End Function
>>>>>>>>
>>>>>>>>
>>>>>>>>Gord Dibben MS Excel MVP
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>On Thu, 13 Jul 2006 14:01:02 -0700, Toppers
>>><Toppers@discussions.micros
>>>>of
>>>>>>t.
>>>>>>co
>>>>>>>>m>
>>>>>>>>wrote:
>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>Try this macro:
>>>>>>>>>
>>>>>>>>>Put this required cell;
>>>>>>>>>
>>>>>>>>>=onelist(A1:A100) .
>>>>>>>>>
>>>>>>>>>.. set range as required
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>Function onelist(ByRef rng As range) as string
>>>>>>>>>bStr = ""
>>>>>>>>> For Each cell In rng
>>>>>>>>> bStr = bStr & Trim(Str(cell.Value)) & ","
>>>>>>>>> Next
>>>>>>>>>onelist = Left(bStr, Len(bStr) - 1)
>>>>>>>>>End Function
>>>>>>>>>
>>>>>>>>>"Colin Hayes" wrote:
>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> Hi All
>>>>>>>>>>
>>>>>>>>>> Hope someone can help.
>>>>>>>>>>
>>>>>>>>>> I have a column of numbers going down the sheet in column A.
>>>>>>>>>>
>>>>>>>>>> I need to concatenate all the numbers in each cell in the column and
>>>>>>>>>> separate each by a comma. This would then be displayed as a long
>list
>>>>>>>>>> all in cell B1.
>>>>>>>>>>
>>>>>>>>>> I've been playing around for ages to do this - can someone put me out
>>>of
>>>>>>>>>> my misery?
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> Best Wishes
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>
>>>>>>
>>>>>>Gord Dibben MS Excel MVP
>>>>
>>>>Gord Dibben MS Excel MVP
>>>
>

Colin

The Function would be available to other workbooks only if the original workbook
was open.

That's why most users will place their macros and UDF's in their Personal.xls
workbook which opens hidden with each session of Excel.

I prefer to keep mine in an Add-in which I load through Tools>Add-ins.

You may or may not have a Personal.xls depending upon whether you have recorded
macros in the past.

To create one...................

Have any workbook open.

Tools>Macro>Record New Macro.

Store in "Personal Macro Workbook"

Record yourself copying a cell and pasting it somewhere.

Stop Recording.

Hit ALT + F11 to go to VBE and you will see a Personal.xls project.

Expand and d-click on Module1

Copy the ConCatRange UDF into that module.

Delete the recorded copy a cell macro lines.

Close the module and hit ALT + Q to go to Excel.

Go to Window and Select Personal.xls and "Hide"

Close Excel and click Yes when asked to save changes to Personal.xls.

Re-open Excel and Personal.xls will open hidden with the UDF and any other
macros you store in there.

The only change you must make is to add to your formula

Personal.xls!ConCatRange(A1:A20)

If you store your macros and UDF's in an Add-in you don't have to preface with a
workbook name.


Gord

On Sat, 15 Jul 2006 20:27:48 +0100, Colin Hayes <Colin@chayes.demon.co.uk>
wrote:

>
>Hi Gord
>
>Ok thanks for getting back.
>
>I went through the process again , and this time it worked fine. i must
>just have made an error when I first did it. Anyway all is well now and
>working perfectly. Thanks!
>
>BTW Once saved as a module in a project , does the coding that we've
>added become embedded in the sheet itself? If it I ran it on another
>machine , would it run independently? Or would i need to put the code in
>again on another PC?
>
>
>
>Best Wishes
>
>
>Colin
>
>
>
>
>
>
>
>In article <6hbib2d4cncm2tpgn6k58uq05pn4bl1m93@4ax.com>, Gord Dibben
><gorddibbATshawDOTca@?.?> writes
>>Colin
>>
>>The green part in the line is caused by the two single quotes I put in telling
>>Excel to ignore that part.
>>
>>Another poster did not want the commas so I remmed that part out and sent you
>>the same code by mistake.
>>
>>You don't want that part ignored, so remove them to this.
>>
>> If Len(cell.text) > 0 Then sbuf = sbuf & cell.text & ","
>>
>>That is not the cause of the #NAME? error however.
>>
>>That error usually comes from Excel not recognizing the function name.
>>
>>Did you Copy/paste to a General Module in the same workbook?
>>
>>If you click on the Fx button and select User Defined from the list, does
>>ConCatRange show up?
>>
>>
>>Gord
>>
>>On Sat, 15 Jul 2006 17:45:24 +0100, Colin Hayes <Colin@chayes.demon.co.uk>
>>wrote:
>>
>>>
>>>HI Gord
>>>
>>>
>>>OK I've entered and saved this function into the code in my workbook :
>>>
>>>Function ConCatRange(CellBlock As Range) As String
>>>Dim cell As Range
>>>Dim sbuf As String
>>> For Each cell In CellBlock
>>> If Len(cell.text) > 0 Then sbuf = sbuf & cell.text '' & ","
>>> Next
>>> ConCatRange = Left(sbuf, Len(sbuf) - 1)
>>>End Function
>>>
>>>
>>>
>>>and entered the formula
>>>
>>>=ConCatRange(A1:A20) in B1
>>>
>>>
>>>I have my numbers down the sheet in column A from A1 to A20.
>>>
>>>
>>>I'm getting the error #NAME? In B1. I've checked all the coding and
>>>formula and all does look well. Any ideas where it might be going wrong?
>>>
>>>
>>>The VBA editor does seem to be giving an error in this phrase :
>>>
>>> If Len(cell.text) > 0 Then sbuf = sbuf & cell.text '' & ","
>>>
>>>It goes green after the word 'text'. If i change any part of this I get
>>>red errors....
>>>
>>>
>>>Thanks again.
>>>
>>>
>>>
>>>Best Wishes
>>>
>>>
>>>Colin
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>In article <IXX50IAMsEuEFwau@chayes.demon.co.uk>, Colin Hayes
>>><Colin@chayes.demon.co.uk> writes
>>>>
>>>>
>>>>Hi Gord
>>>>
>>>>OK Thanks for that. Very helpful.
>>>>
>>>>
>>>>
>>>>Best Wishes
>>>>
>>>>
>>>>Colin
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>In article <e8kfb2p51fl0hu7676kg80tbn9fc493vck@4ax.com>, Gord Dibben
>>>><gorddibbATshawDOTca@?.?> writes
>>>>>Colin
>>>>>
>>>>>Apologies for the misunderstanding.
>>>>>
>>>>>I saw a post from you asking Toppers for instructions on how to implement the
>>>>>Function and my assumer got stuck.
>>>>>
>>>>>If not familiar with macros and VBA, visit David McRitchie's website on
>>>>>"getting started".
>>>>>
>>>>>http://www.mvps.org/dmcritchie/excel/getstarted.htm
>>>>>
>>>>>In the meantime..........
>>>>>
>>>>>To create a General Module, hit ALT + F11 to open the Visual Basic Editor.
>>>>>
>>>>>Hit CRTL + R to open Project Explorer.
>>>>>
>>>>>Find your workbook/project and select it.
>>>>>
>>>>>Right-click and Insert>Module. Paste the ConCatRange function code in there.
>>>>>
>>>>>Save the workbook and hit ALT + Q to return to your workbook.
>>>>>
>>>>>Enter the formula =ConCatRange(A1:A20) in B1
>>>>>
>>>>>
>>>>>Gord
>>>>>
>>>>>On Fri, 14 Jul 2006 14:24:40 +0100, Colin Hayes
>>>><Colin@chayes.demon.co.uk>
>>>>>wrote:
>>>>>
>>>>>>In article <5o5eb29l7bki7a0778mbe6bnonca5rbd1f@4ax.com>, Gord Dibben
>>>>>><gorddibbATshawDOTca@?.?> writes
>>>>>>>Your choice.
>>>>>>>
>>>>>>>Have fun.
>>>>>>>
>>>>>>>Gord
>>>>>>
>>>>>>HI Gord
>>>>>>
>>>>>>No , not my choice at all. Quite the opposite in fact. I spent some time
>>>>>>trying to implement the code , and wasn't able to.
>>>>>>
>>>>>>I'm grateful for any advice given of course , but we're not all experts.
>>>>>>MVPs have a role as instructor , don't they?
>>>>>>
>>>>>>
>>>>>>Best Wishes
>>>>>>
>>>>>>
>>>>>>Colin
>>>>>>
>>>>>>
>>>>>>>
>>>>>>>On Fri, 14 Jul 2006 03:47:40 +0100, Colin Hayes
>>>>><Colin@chayes.demon.co.uk>
>>>>>>>wrote:
>>>>>>>
>>>>>>>>
>>>>>>>>HI All
>>>>>>>>
>>>>>>>>I'm pleased you were able to sort that out between yourselves...!
>>>>>>>>
>>>>>>>>I'll do it manually ....
>>>>>>>>
>>>>>>>>^_^
>>>>>>>>
>>>>>>>>Best Wishes
>>>>>>>>
>>>>>>>>
>>>>>>>>Colin
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>In article <eqidb2hjmtvp363a8ncpunpgt5gprh2kd6@4ax.com>, Gord
>>Dibben
>>>>>>>><gorddibbATshawDOTca@?.?> writes
>>>>>>>>>Toppers
>>>>>>>>>
>>>>>>>>>One problem with this.
>>>>>>>>>
>>>>>>>>>If a cell in the range is blank it returns a 0
>>>>>>>>>
>>>>>>>>>So you get 1,2,3,0,0,0,7,8,9 if A4:A6 are blank.
>>>>>>>>>
>>>>>>>>>This function ignores blanks.
>>>>>>>>>
>>>>>>>>>Function ConCatRange(CellBlock As Range) As String
>>>>>>>>>Dim cell As Range
>>>>>>>>>Dim sbuf As String
>>>>>>>>> For Each cell In CellBlock
>>>>>>>>> If Len(cell.text) > 0 Then sbuf = sbuf & cell.text '' & ","
>>>>>>>>> Next
>>>>>>>>> ConCatRange = Left(sbuf, Len(sbuf) - 1)
>>>>>>>>>End Function
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>Gord Dibben MS Excel MVP
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>On Thu, 13 Jul 2006 14:01:02 -0700, Toppers
>>>><Toppers@discussions.micros
>>>>>of
>>>>>>>t.
>>>>>>>co
>>>>>>>>>m>
>>>>>>>>>wrote:
>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>Try this macro:
>>>>>>>>>>
>>>>>>>>>>Put this required cell;
>>>>>>>>>>
>>>>>>>>>>=onelist(A1:A100) .
>>>>>>>>>>
>>>>>>>>>>.. set range as required
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>Function onelist(ByRef rng As range) as string
>>>>>>>>>>bStr = ""
>>>>>>>>>> For Each cell In rng
>>>>>>>>>> bStr = bStr & Trim(Str(cell.Value)) & ","
>>>>>>>>>> Next
>>>>>>>>>>onelist = Left(bStr, Len(bStr) - 1)
>>>>>>>>>>End Function
>>>>>>>>>>
>>>>>>>>>>"Colin Hayes" wrote:
>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> Hi All
>>>>>>>>>>>
>>>>>>>>>>> Hope someone can help.
>>>>>>>>>>>
>>>>>>>>>>> I have a column of numbers going down the sheet in column A.
>>>>>>>>>>>
>>>>>>>>>>> I need to concatenate all the numbers in each cell in the column and
>>>>>>>>>>> separate each by a comma. This would then be displayed as a long
>>list
>>>>>>>>>>> all in cell B1.
>>>>>>>>>>>
>>>>>>>>>>> I've been playing around for ages to do this - can someone put me out
>>>>of
>>>>>>>>>>> my misery?
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> Best Wishes
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>
>>>>>>>
>>>>>>>Gord Dibben MS Excel MVP
>>>>>
>>>>>Gord Dibben MS Excel MVP
>>>>
>>