Hi,

I would want to copy consistent data from a column of unknown length to other sheet, but generating X blank rows between any two lines of data.Any help appreciated !

Here's a fun way: In a separate column, enter sequential numbers alongside your data (1,2,3, etc).

Copy the numbers and paste them underneath the original list of numbers the same amount of times as you want blank rows between the data.

Sort the Rows based on this column of numbers and you'll have your gaps.

Delete the column of numbers.

Let's say you start with this in Column A, and you want 2 empty rows between each piece of data:

A X Y Z

Do this first:

A B X 1 Y 2 Z 3Then Copy/Paste to get this:

A B X 1 Y 2 Z 3 1 2 3 1 2 3

Sort both columns by Column B to get this:A B X 1 1 1 Y 2 2 2 Z 3 3 3Delete Column B

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

Hi DerbyDad3,

Thank you for your brilliantly simple solution !

I dare to ask your help to automate it when copying to the other sheet, may I ?

(usualy, the original column is of an unknown length and X is 10 in my case).

Thank you in advance ! Sam

What version of Excel are you using? If the length of the data is "unknown", how will you know if you can fit 10 rows between each piece of data before you hit the limit of rows allowed in a spreadsheet?

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

Sub GapMaker() Dim lastRw, nxtRw As Integer 'Determine length of Column A lastRw = Range("A" & Rows.Count).End(xlUp).Row 'Place sequential numbers in Column B With Range("B1:B" & lastRw) .Formula = "=Row()" .Copy .PasteSpecial xlPasteValues End With 'Initialize Next Row Counter nxtRw = 1 'Copy/Paste Sequential numbers 10 times For Gap = 1 To 10 nxtRw = nxtRw + lastRw Range("B" & nxtRw & ":B" & nxtRw + lastRw).PasteSpecial xlPasteValues Next 'Sort on Column B and Clear Column B Columns("A:B").Sort Key1:=Range("B1") Columns("B:B").ClearContents End Sub

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

Ask Your Question

Weekly Poll