Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Concatenation of cells, automatically please (Excel 2000, Win 2000)

    Hello All

    I have a need to concatenate a whole load of cells (300 plus) into one string, the contents of each cell must be separated by a comma followed by a space. Is there any way I can do this with code?? I've got up to about the 40th cell and I'm fed up already!!!

    Thanks in advance.

    Ian

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Concatenation of cells, automatically please (Excel 2000, Win 2000)

    See <post:=529,227>post 529,227</post:>. You can use the custom worksheet function from that post in a formula, for example
    <code>
    =Concatenate_Range(A1:A345,", ")</code>

  3. #3
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenation of cells, automatically please (Excel 2000, Win 2000)

    Hans

    Thanks for that, but I'm getting error messages, not concatenated strings....

    The errors are: -

    expected line number or lable or statement or end of statement if I use =concatenate_range(a1:a1,", ") The = is highlighted.
    expected list separator if I use concatenate_range(a1:a1,", ") The : is highlighted.

    I not got a clue why either of these are showing!!!!

    Ian

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Concatenation of cells, automatically please (Excel 2000, Win 2000)

    It doesn't make sense to concatenate the range A1:A1: it consists of only a single cell.

    You are supposed to put the code in a module and to place the formula = Concatenate_Range(A1:A10,", ") or similar in a cell in a worksheet.

    If you wish, you can attach (a stripped down copy of) your workbook.

  5. #5
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenation of cells, automatically please (Excel 2000, Win 2000)

    <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30> <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    So, that's why it doesn't work from a command button or while being called as a macro then!!!!!!!!! I'll go back to my box now.

    Just followed the instructions and it worked perfectly, you are my hero yet again Hans.

    Thanks

    Ian

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Concatenation of cells, automatically please (Excel 2000, Win 2000)

    You can use the function in VBA code too, but in that case you must provide a range object as first argument, for example:

    Sub MyMacro()
    Range("B1") = Concatenate_Range(Range("A1:A100"), ", ")
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •