+ Reply to Thread
Results 1 to 2 of 2

Duplicate rows if cell contains carriage return (or possibly newline)

Hybrid View

  1. #1
    Registered User
    Join Date
    12-15-2016
    Location
    Santiago, Chile
    MS-Off Ver
    Office 365
    Posts
    1

    Duplicate rows if cell contains carriage return (or possibly newline)

    Hi,

    I would like to know how I can duplicate a row if a cell in a particular column has multiple values separated by a carriage return (or maybe its an endline not sure). My data looks something like this:

    uniquetext_A text1
    text2
    uniquetext_B text1
    uniquetext_C text1
    text2
    text3

    and the result would ideally be:

    uniquetext_A text1
    uniquetext_A text2
    uniquetext_B text1
    uniquetext_C text1
    uniquetext_C text2
    uniquetext_C text3


    I've already tried this:
    
    Sub SplitCarriages() 
        Dim mySplit() As String 
        Dim i As Long, txtCount As Long 
        Dim lastRow As Long, recRow As Long 
        Dim searchCol As String 
        Dim sourceWS As Worksheet, dumpWS As Worksheet 
         
         
         'Which worksheet as we getting data from?
        Set sourceWS = Worksheets("Sheet1") 
         'Which worksheet are we putting data in?
        Set dumpWS = Worksheets("Sheet2") 
         'Which column has carraige returns?
        searchCol = "D" 
         
         
        Application.ScreenUpdating = False 
        dumpWS.Select 
        recRow = 0 
        With sourceWS 
            lastRow = .Cells(.Rows.Count, searchCol).End(xlUp).Row 
             
             'Starting at row 1, presumably
            For i = 1 To lastRow 
                mySplit = Split(.Cells(i, searchCol), Chr(10)) 
                For txtCount = LBound(mySplit) To UBound(mySplit) 
                    recRow = recRow + 1 
                     'Copy whole row
                    .Cells(i, 1).EntireRow.Copy Cells(recRow, 1) 
                     'Place single line of text
                    Cells(recRow, searchCol).Value = mySplit(txtCount) 
                Next txtCount 
            Next i 
        End With 
        Application.ScreenUpdating = True 
         
         
    End Sub
    And I'm having partial success, with problems though.
    Here are my before-after macro pictures of the sheet, and also a link to the original file.

    Original: https://drive.google.com/open?id=0B6...kYyTU9XLV9HQ2M
    Post-Macro: https://drive.google.com/open?id=0B6...WdRY29IWGZxd28

    Original File: https://drive.google.com/open?id=0B6...21raWpuTUI5STA

    Regards!
    Last edited by matybg; 12-15-2016 at 04:07 PM.

  2. #2
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: Duplicate rows if cell contains carriage return (or possibly newline)

    Im unable to open google drive docs due to our firewall can you upload the workbook?
    If someone has helped you then please add to their Reputation

+ 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] Cocantenate with Carriage Return (Loop Through Rows)
    By desertshore in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-17-2013, 03:54 PM
  2. carriage return in a text cell
    By dockdude in forum Excel General
    Replies: 1
    Last Post: 02-25-2011, 04:12 PM
  3. Carriage return splitting into different rows
    By [email protected] in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-15-2011, 01:29 AM
  4. [SOLVED] Carriage Return in cell
    By g48dd in forum Excel General
    Replies: 3
    Last Post: 12-28-2007, 02:24 AM
  5. How do you put a carriage return into some cell data?
    By iterature in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-26-2007, 07:49 AM
  6. Carriage Return within Cell for MAC users
    By SAP2112 in forum Excel General
    Replies: 0
    Last Post: 07-05-2006, 06:08 PM
  7. Replies: 6
    Last Post: 03-23-2005, 11:06 PM
  8. Replies: 3
    Last Post: 02-14-2005, 09:06 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