+ Reply to Thread
Results 1 to 6 of 6

How to shorten code into an array to create a list of items separated by commas

  1. #1
    Registered User
    Join Date
    10-09-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    78

    How to shorten code into an array to create a list of items separated by commas

    I want a cell to be populated with words in other cells to create a list like this: "Apple, Pear, Banana, ETC...." I have created code that I am pretty sure will do this but it is very long and very messy. The one cell is being populated by multiple cells which hold a different word e.g a different fruit name. I need an Array which puts them all into the same cell separated with a comma. "lngWriteRow" can be ignored for now this just tells the code to input the list in the next empty row of column "E". Here is the code I have currently come up with:

    Please Login or Register  to view this content.
    Last edited by mdovey; 04-21-2014 at 12:23 PM.

  2. #2
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: How to shorten code into an array to create a list of items separated by commas

    Because the content is a string, to give visibility, why don't you build the string step by step
    See here after: NOT TESTED
    Please Login or Register  to view this content.

  3. #3
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: How to shorten code into an array to create a list of items separated by commas

    Perhaps :-
    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    01-25-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2016
    Posts
    166

    Re: How to shorten code into an array to create a list of items separated by commas

    I don't know if this is shorter or better, but it works

    Please Login or Register  to view this content.
    stemp will contain the string you need then you can put it wherever you want it. If there is a fruit in every cell then you can eliminate the if "" test.

    Regards,

    Tom

  5. #5
    Registered User
    Join Date
    10-09-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: How to shorten code into an array to create a list of items separated by commas

    Both helpful ideas thank you very much. I ended up using the second one as it seemed pretty straight forward and was much shorter than my original code.

  6. #6
    Registered User
    Join Date
    10-09-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: How to shorten code into an array to create a list of items separated by commas

    Ha Ill take your word for it Tom I'm pretty new to VBA so most of that goes straight over my head. Thanks for another approach though.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Using an array to create a list of selected items
    By Harribone in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-02-2014, 04:40 PM
  2. Replies: 5
    Last Post: 01-17-2014, 05:56 PM
  3. Search for comma-separated list of items
    By BBerndsen in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-15-2013, 11:16 AM
  4. Replies: 5
    Last Post: 06-05-2012, 03:32 PM
  5. How to make a list of words separated by commas?
    By Alle in forum Excel General
    Replies: 2
    Last Post: 08-28-2011, 05:39 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1