We help IT Professionals succeed at work.

# Making An Excel Row Into Two Rows

on
I am using Excel 2007.  I have a spreadsheet containing many rows.  Each rowcontains the columns col1,col2,col3,col4,col5,col6.  I want to create two seperate rows for each row.  The first row should contain columns col1,col2,col3 and the second row should contain columns col4,col5 and col6.  How can I do this in Excel?
Comment
Watch Question

## View Solution Only

Most Valuable Expert 2011
Awarded 2010

Commented:
Hello,

with your list starting in A1, put this formula in H1

=INDEX(\$A:\$F,CEILING(ROW()/2,1),(MOD(ROW()-1,2)*3)+COLUMN(A1))

copy across to J1 and down. Copy the result and use Paste Special > Values to paste only the results.

cheers, teylyn
Solutions Consultant
Commented:
The attached sub will perform the actions you require.

I have also attached an example file so you can see it in action

Michael
``````Sub MoveData()
Dim currentRow As Long, i As Long

i = 3 + 1
While Not IsEmpty(Range("A" & i).Value)
rows(i & ":" & i + 2).Insert
i = i + 6
Wend

For i = 1 To Range("A" & rows.Count).End(xlUp).Row Step 6
Range("D" & i, "F" & i + 2).Cut Range("A" & i + 3)
Next

End Sub
``````
Move-Data-Example.xls

Commented:
What if I have a total of 10, 20 or some other number of columns and I want the first half (ie columns 1-10) to be in one row and the second half (ie columns 11-20) in the next row, how could the above command be used for for any number of columns?
Solutions Consultant

Commented:
I changed the macro so that now it needs to be called with the column number that the is the start of the columns to be moved so from your first example that is 4 and the column number of the last column to move ie 6 from the first example.
It then inserts these columns under the current data as requested

Michael
``````Sub MoveData(startCol As Long, endCol As Long)
Dim currentRow As Long, i As Long, colNumbers As Long

colNumbers = endCol - startCol + 1

i = colNumbers + 1
While Not IsEmpty(Range("A" & i).Value)
rows(i & ":" & i + colNumbers - 1).Insert
i = i + colNumbers * 2
Wend

For i = 1 To Range("A" & rows.Count).End(xlUp).Row Step 6
Range(Cells(i, startCol), Cells(i + colNumbers - 1, endCol)).Cut Range("A" & i + colNumbers)
Next

End Sub

Sub test()
Call MoveData(4, 6)
End Sub
``````
Most Valuable Expert 2011
Awarded 2010

Commented:
In your question it originally said that you wanted to do this without programming. Are you still interested in a formula solution?
Solutions Consultant

Commented:
Here is a slightly more generic version. numUntouchedCols is the number of columns to be left in place. ie 3 in your first example

@teylyn - Maybe I am going mad but I took the original question  to mean that a VBA or formula solution would be acceptable

@zabac - If I missed the point here please ensure all the points go to teylyn

Michael
``````Sub MoveData(numUntouchedCols, startCol As Long, endCol As Long)
Dim currentRow As Long, i As Long, moveCols As Long

moveCols = endCol - startCol + 1

i = numUntouchedCols + 1
While Not IsEmpty(Range("A" & i).Value)
rows(i & ":" & i + moveCols - 1).Insert
i = i + numUntouchedCols * 2
Wend

i = 1

For i = 1 To Range("A" & rows.Count).End(xlUp).Row Step endCol
Range(Cells(i, startCol), Cells(i + moveCols - 1, endCol)).Cut Range("A" & i + moveCols)
Next

End Sub

Sub test()
Call MoveData(3, 4, 6)
End Sub
``````