Inserting a specific number of blank rows between cells - Excel 2007

[excelactuary]

Hello!

I have a worksheet with four data columns A,B,C,D starting in row 2. I want to add between every row, exactly 11 blank rows. So if we have:

A B C D

1 5 3 4
2 7 6 3
3 9 1 3

the end result should be:

A B C D

1 5 3 4











2 7 6 3












3 9 1 3

Do I necessarily need a macro for this? Or is there another quick and creative way to achieve this result?

Thank you for your time!

Regards,

excelactuary

[Pete_UK]

Use column E as a helper column and fill a simple sequence down to the bottom of your data - in your example it would just be 1, 2, 3, and cell E5 would be the next available cell. Then <copy> the sequence, move the cursor to the next available cell in the column (press END followed by down-arrow, then down-arrow again) and paste the sequence with CTRL-V, then repeat this ten more times. Then highlight all your data including the helper column, and sort the data using column E as the sort field. At the end you can delete column E, leaving you with what you wanted.

Hope this helps.

Pete

[jraj1106]

Hi.

I have this macro code. You can try this too:

Sub INSERTBLANKS()
    Dim rcnt As Long
    
    rcnt = Range("A" & Rows.Count).End(xlUp).Row
    i = 2
    
    While Range("A" & i) <> ""
        For j = 1 To 11
            Rows(i + 1).Select
            Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Next
        i = j + i
    Wend
End Sub

[excelactuary]

Thanks a lot!