+ Reply to Thread
Results 1 to 6 of 6

Split Single Row into multiple rows based on column data

Hybrid View

  1. #1
    Registered User
    Join Date
    09-05-2012
    Location
    Dallas, Texas
    MS-Off Ver
    Excel 2013
    Posts
    71

    Split Single Row into multiple rows based on column data

    I need to modify raw data that is pulled from online submissions. The data is for tournament entries that can have a single person up to a team of 3 that exports all to one line. Entries with 2 or 3 participants need to be cut and inserted into rows below the active row. I found code that works properly for data in Col U-AF but pastes data from Col AG-AR wrong (code pasted below).
    • The number of rows for the original raw data will vary.
    • Each row will always have data in Col A-T.
    • The length of each row will contain data for 1 to 3 people.
    • Participants 2 & 3 need to be pasted on new rows starting at col F.
    Example:
    1. If column U-AF contains data then:
    (1) Insert ONE row below
    (2) Cut/Paste data from col U-AF into NEW row starting at col F.
    2. If there is no data in col U then move to next column.
    3. If col U-AR contains data then:
    (1) Insert TWO rows below.
    (2) Cut/Paste data from Col U-AF in the FIRST NEW row.
    (3) Cut/Paste data from Col AG-AR in the SECOND NEW row.

    Sub MoveData()
        Dim loopRws As Integer
        Dim srcRw As Long
        Dim lastCol As Long
        Dim dstRws As Integer
        Dim loopCnt As Integer
        Dim dstRw As Integer
        Dim srcCol As Integer
        Application.ScreenUpdating = True
    
        'Determine how many rows we need to loop through
        'Assume Column headers in Row 1
            loopRws = Range("A" & Rows.Count).End(xlUp).Row - 1
        
        'Loop through source Rows
          For srcRw = 3 To Rows.Count
        
        'Determine how many Rows to Insert based on how much data is in current Row
                lastCol = 44
                dstRws = Application.WorksheetFunction.RoundDown((lastCol - 21) / 12, 0)
        
        'If no data past Column K, no Insert needed
            If dstRws < 1 Then
               loopCnt = loopCnt + 1
               GoTo NoInsert
            End If
        
        'Insert Rows based on width of data in current Row
              Rows(srcRw & ":" & srcRw + dstRws).Insert
        
        'Initialize variable for first Paste row
               dstRw = srcRw
        
        'Cut(Copy) data to Column F
                 For srcCol = 21 To lastCol Step 5
                   Range(Cells(srcRw - 1, srcCol), Cells(srcRw - 1, srcCol + 11)).Copy _
                      Destination:=Cells(dstRw, 6)
    '                Range(Cells(srcRw - 1, srcCol), Cells(srcRw - 1, srcCol + 11)).Cut _
    '                  Destination:=Cells(dstRw, 6)
                   dstRw = dstRw + 1
                 Next
        
        'Set new Source Row
           srcRw = srcRw + dstRws + 1
        
        'Count loops, exit when last Row has been done
            loopCnt = loopCnt + 1
              If loopCnt = loopRws Then Exit For
    NoInsert:
         Next
         
    End Sub

  2. #2
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Split Single Row into multiple rows based on column data

    See if this works for you.

    Sub reorgData()
    Dim sh As Worksheet, rng As Range, lr As Long
    Set sh = ActiveSheet
    Set rng = sh.Range("A3", sh.Cells(Rows.Count, 1).End(xlUp).Offset(, sh.UsedRange.Columns.Count - 1))
    lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
        For i = lr To 3 Step -1
            If Application.CountA(Rows(i)) > 32 Then
                With sh
                    .Cells(i + 1, 1).Resize(2, 1).EntireRow.Insert
                    .Range(.Cells(i, 21), .Cells(i, 32)).Cut .Cells(i + 1, 6)
                    .Range(.Cells(i, 33), .Cells(i, 44)).Cut .Cells(i + 2, 6)
                End With
            ElseIf Application.CountA(Rows(i)) > 20 Then
                With sh
                    .Cells(i + 1, 1).EntireRow.Insert
                    .Range(.Cells(i, 21), .Cells(i, 32)).Cut .Cells(i + 1, 6)
                End With
            End If
        Next
    End Sub

  3. #3
    Registered User
    Join Date
    09-05-2012
    Location
    Dallas, Texas
    MS-Off Ver
    Excel 2013
    Posts
    71

    Re: Split Single Row into multiple rows based on column data

    It worked on every row but the first (row 2). There is still data in columns after T. Is there any way to fix that?

  4. #4
    Registered User
    Join Date
    09-05-2012
    Location
    Dallas, Texas
    MS-Off Ver
    Excel 2013
    Posts
    71

    Re: Split Single Row into multiple rows based on column data

    Here is a snap of what it was doing. Forum - Tourney Snap.jpg

  5. #5
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Split Single Row into multiple rows based on column data

    I was basing the source range on the code in post #1 where it indicates that source data begins in row 3. Is that incorrect? According to the screen shot in post #3 , row 1 and 2 are header rows and data begins in row 3, so the code should be returning any data found in row 3 on an inserted row if it exceeds the 21 cell limit.

  6. #6
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Split Single Row into multiple rows based on column data

    BTW, the rng variable wasn't used, so here is a revised code which excludes that bit.
    Sub reorgData()
    Dim sh As Worksheet, lr As Long
    Set sh = ActiveSheet
    lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
        For i = lr To 2 Step -1
            If Application.CountA(Rows(i)) > 32 Then
                With sh
                    .Cells(i + 1, 1).Resize(2, 1).EntireRow.Insert
                    .Range(.Cells(i, 21), .Cells(i, 32)).Cut .Cells(i + 1, 6)
                    .Range(.Cells(i, 33), .Cells(i, 44)).Cut .Cells(i + 2, 6)
                End With
            ElseIf Application.CountA(Rows(i)) > 20 Then
                With sh
                    .Cells(i + 1, 1).EntireRow.Insert
                    .Range(.Cells(i, 21), .Cells(i, 32)).Cut .Cells(i + 1, 6)
                End With
            End If
        Next
    End Sub
    I just took a closer look at your screen shot. The code above has been modified to use row 2 as the first source row.
    Last edited by JLGWhiz; 04-29-2017 at 02:01 PM.

+ 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. Replies: 1
    Last Post: 04-06-2016, 07:02 AM
  2. Need Macro code to split data split in 7 sheets based on variable rows in column A
    By Alija_21 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-31-2015, 08:03 AM
  3. [SOLVED] Multiple values in single cell (known column) split into rows with duplicate information
    By bwashbourne in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-06-2015, 05:00 PM
  4. Split Data From Single Column Set Into Multiple Column Sets?
    By slavrenz in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-25-2014, 04:33 AM
  5. split single spreadsheet into multiple workbooks based on value in column D
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-22-2014, 07:27 PM
  6. Replies: 10
    Last Post: 07-22-2012, 07:32 PM
  7. Want to split the data in single cell to multiple column
    By pradeepdeepu_001 in forum Excel General
    Replies: 4
    Last Post: 02-02-2010, 09:11 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