gaps in copying data

February 18, 2011 at 05:37:31
Specs: Windows XP
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 !


See More: gaps in copying data

Report •

#1
February 18, 2011 at 08:14:40
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  3

Then 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
   3

Delete Column B

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


Report •

#2
February 18, 2011 at 09:56:27
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

Report •

#3
February 18, 2011 at 10:35:46
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.


Report •

Related Solutions

#4
February 18, 2011 at 11:58:57
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.


Report •

Ask Question