+ Reply to Thread
Results 1 to 7 of 7

Split Delimited Data in Columns into New Rows and keep all other data

  1. #1
    Registered User
    Join Date
    10-07-2020
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    4

    Split Delimited Data in Columns into New Rows and keep all other data

    HI,

    I'm new to VBA and I am trying to split the data in my columns into separate rows while keeping the 1st column as well as the header intact. I have an existing code which allows me to split column B but I'm trying to edit the code such that I am able to split the data starting from Column B all the way to the last column. Can anyone kindly advice on how to do that? I've attached the excel file with the data as well as the below macro already in there.

    Thank you and I sincerely appreciate the help!

    ____________________________________________________________________________________________________________________________

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by alansidman; 10-08-2020 at 05:22 AM.

  2. #2
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,285

    Re: Split Delimited Data in Columns into New Rows and keep all other data

    You will be asked to add code tags - see rules - to make for easier reading

    is this what you want
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    10-07-2020
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    4

    Re: Split Delimited Data in Columns into New Rows and keep all other data

    I'm so sorry, this is the first time that I am posting a coding question so I'm not familiar with code tags...

    I would like to split the data in columns C, D, E, F, all the way to the last column that has data into rows but keeping the header as well as the information in column A. I've attached an example of how Column B should look like after splitting. Column C, D, E and so forth should follow suit and split into rows as well. The number of columns should still remain the same, just the number of rows will increase. I hope my explanation is clear...

    Attachment 698587
    Last edited by VBA_123; 10-07-2020 at 07:24 AM.

  4. #4
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,285

    Re: Split Delimited Data in Columns into New Rows and keep all other data

    Hi, Attachment invalid so cant see. I'm not sure I understand your request either

  5. #5
    Registered User
    Join Date
    10-07-2020
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    4

    Re: Split Delimited Data in Columns into New Rows and keep all other data

    May I know if you are able to open the attached excel file testdatabase.xlsm?
    The macro "replicate data" does exactly what I want, just that it now only works on column B.
    I would like it to do the same for Column C, D, E (splitting by delimiter) until the last column (which in this case is column P) that has data in it.
    Attached Files Attached Files
    Last edited by VBA_123; 10-07-2020 at 07:40 AM.

  6. #6
    Registered User
    Join Date
    10-07-2020
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    4

    Re: Split Delimited Data in Columns into New Rows and keep all other data

    Post deleted
    Last edited by VBA_123; 10-08-2020 at 12:14 AM. Reason: Delete post as I realise my logic for what I was trying to achieve is wrong

  7. #7
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,689

    Re: Split Delimited Data in Columns into New Rows and keep all other data

    Code Tags Added
    Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



    (I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

+ 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. split delimited data into rows - how???
    By elizevonne in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-31-2020, 06:25 PM
  2. [SOLVED] Split data from columns to rows
    By tax112 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-04-2019, 05:44 AM
  3. [SOLVED] Split Delimited nth Data into New Rows/columns
    By adeel1 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-29-2018, 01:55 AM
  4. Vba code to split data with delimited "," in separate rows
    By gaikwad.mm@gmail.com in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 04-26-2017, 02:26 AM
  5. Delimited Data - Columns and Rows
    By Arch88 in forum Excel General
    Replies: 1
    Last Post: 06-01-2016, 07:11 AM
  6. [SOLVED] Multi columns of delimited data into rows
    By DWTK in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-18-2013, 11:18 AM
  7. Split one row of data to columns and rows
    By A7md in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-12-2011, 05:52 PM

Tags for this Thread

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