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
Bookmarks