Closed Thread
Results 1 to 7 of 7

VBA Split Cell Contents to New Rows & Copy Cells containing single values to the new rows

  1. #1
    Registered User
    Join Date
    09-20-2013
    Location
    McKees Rocks, PA
    MS-Off Ver
    Excel 2013
    Posts
    19

    VBA Split Cell Contents to New Rows & Copy Cells containing single values to the new rows

    Please forgive me for any errors in improper posting. This is my first post.

    I have a spreadsheet where some cells contain multiple lines of data separated by alt+enter. The data is contained in columns A:Q. The number of values in a cell varies, but for each line if any lines contain multiple values, the other lines will contain either nothing (blank), 1 value, or the same number of values as the other cells containing multiple values.
    I want a macro to be able to split the cells that contain multiple lines of data into new rows and copy the remaining cells into the new rows.

    Here is a sample of what I have although my data actually goes through column Q:

    A B C D E F
    1 abc 123
    456
    789
    012
    pqr
    stu
    vwx
    yza
    A C
    2 def 123
    456
    789
    012
    345
    abc
    def
    ghi
    jkl
    mno
    ABC
    DEF
    GHI
    JKL
    MNO
    F


    I want the output to look like this:

    A B C D E F
    1 abc 123 pqr A C
    2 abc 456 stu A C
    3 abc 789 vwx A C
    4 abc 012 yza A C
    5 def 123 abc ABC F
    6 def 456 def DEF F
    7 def 789 ghi GHI F
    8 def 012 jkl JKL F
    9 def 345 mno MNO F


    I am new to VBA, but copied this from another thread.

    Please Login or Register  to view this content.
    It works fine for columns A:E, but when I try to add additional columns like below, I get a Run Time Error '9' Subscript out of Range with line "Cells(i + 1, "F") = MyArr5(r)" highlighted.

    Please Login or Register  to view this content.
    I feel like I am missing something simple. Can anyone please help me out?

    Thanks!
    Last edited by jaimelwilson; 09-28-2013 at 11:22 AM.

  2. #2
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: VBA Split Cell Contents to New Rows & Copy Cells containing single values to the new r

    Rewrote the whole thing, try this. Should work for any number of columns.
    Please Login or Register  to view this content.
    Last edited by millz; 09-28-2013 at 09:56 AM.

  3. #3
    Registered User
    Join Date
    09-20-2013
    Location
    McKees Rocks, PA
    MS-Off Ver
    Excel 2013
    Posts
    19

    Re: VBA Split Cell Contents to New Rows & Copy Cells containing single values to the new r

    This is amazing! Thank you so much!
    One more quick question if you don't mind. I have a few cells that have an extra Alt+enter at the end which is creating extra rows that I don't really need. Is there a simple way to find and delete these alt+enter that are at the end of the strings?
    Thanks again!

  4. #4
    Registered User
    Join Date
    09-20-2013
    Location
    McKees Rocks, PA
    MS-Off Ver
    Excel 2013
    Posts
    19

    Re: VBA Split Cell Contents to New Rows & Copy Cells containing single values to the new r

    I marked this as solved and created a new thread to address my last question since it is mostly unrelated to my initial question. The thread is located here:
    http://www.excelforum.com/excel-prog...12#post3421312

  5. #5
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: VBA Split Cell Contents to New Rows & Copy Cells containing single values to the new r

    I see you have your problem solved in the other thread, that's great. If you need any help combining that into this code let me know.

  6. #6
    Registered User
    Join Date
    06-13-2017
    Location
    Richmond, VA
    MS-Off Ver
    2010
    Posts
    1

    Re: VBA Split Cell Contents to New Rows & Copy Cells containing single values to the new r

    Can someone please update the code above to ONLY split out the contents of column N if there is a Alt+enter?

  7. #7
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: VBA Split Cell Contents to New Rows & Copy Cells containing single values to the new r

    C_W,
    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Ben Van Johnson

Closed Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Macro to split a single cell data into rows and copying other cells
    By CVARGA66 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-10-2012, 04:29 PM
  2. Split cell contents over multiple rows based on cell contents
    By naigy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-17-2011, 05:38 PM
  3. Split cell data into multiple new rows and copy other column values
    By jooga in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-01-2010, 02:30 AM
  4. Macro to split a single cell data into rows and copying other cells as it is in rows
    By Pankaj Sonawane in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 08-25-2010, 07:09 PM
  5. Replies: 1
    Last Post: 03-18-2008, 07:03 PM

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