Inserting multiple rows in between data in excel

Question

Hi, I have 140 rows of data and I want to insert 6 lines between each row of data, is there a quick way of doing this ?

I want to copy and paste the data from the 140 rows into another data table which is set out with Row 1 - 6 for example for each row of data.

Original Data

Row 1

Row 2

Row 3

Row 4

How I want it to look

Row 1

Row 2

Row 3

Row 4

Thank you

Answer 1

Maureen,

Most simple, for a one time event, I think is to do the following:

I assume your data are in A1:A140

Then put a 1 in B1 and drag this down to B140 so that the result is a numbering from 1 to 140.

Copy B1:B140 and paste it six times under B140.

Then select B1 and sort your data.

Delete the numbers in column B and you are done.

Jan

Answer 2

Hi Maureen

If you have to do the task multiple times then you need a VBA macro

If so you may try the following code

''''''**********************************************************

Sub InsertMultipleRows()

Dim Lines As Long

Dim lastRow As Long

Dim inputSh As Worksheet

Dim outputSh As Worksheet

On Error GoTo getOut

Lines = InputBox(Prompt:="How many rows you want to insert?", Title:="INSERT LINES")

Set inputSh = ThisWorkbook.Sheets("Input Data")

Set outputSh = ThisWorkbook.Sheets("Output Data")

outputSh.Cells.Clear

With inputSh

lastRow = .Cells.Find("\*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 

    For x = 1 To lastRow 

            .Cells(x, "A").EntireRow.Copy outputSh.Cells((Lines + 1) \* x - Lines, "A") 

    Next x 

End With

MsgBox "Job Done"

getOut:

End Sub

'''''*************************************************************************************

This code will prompt an input box where you will enter the number of rows you want between each data line

RESULT

You may download the sample workbook here https://we.tl/t-O2oSmKd0Va

Regards

Jeovany

Answer 3

Hi,

You may download my solution workbook from here. I have solved the problem using the Query Editor.

Hope this helps.