Results 1 to 2 of 2

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

Threaded 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.

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