CODEWITHSUNDEEP
excel
Andre van Rensburg
Andre van Rensburg

Reputation: 161

How to copy each row in excel and paste it under the copied row

I am looking for a way to copy each row in a sheet and paste it under the original row.

CURRENTLY:

1 | abc | def
2 | ghi | jkl
3 | mno | pqr

WHAT I AIM TO DO:

1 | abc | def
2 | abc | def
3 | ghi | jkl
4 | ghi | jkl
5 | mno | pqr
6 | mno | pqr

Hope this makes sense!

Upvotes: 0

Views: 9012

Answers (3)

QHarr
QHarr

Reputation: 84465

With vba assuming no gaps in range

Option Explicit

Sub Testing()

Dim targetRange As Range
Dim sourceData()

With ActiveSheet.Range("A1").CurrentRegion

    sourceData = .Value2
    Set targetRange = .Resize(.Rows.Count * 2, .Columns.Count)

End With

Dim currValue As Long
Dim counter As Long
counter = 1

For currValue = LBound(sourceData, 1) To UBound(sourceData, 1)

    targetRange.Cells(counter, 1).Resize(2, UBound(sourceData, 2)) = Application.WorksheetFunction.Index(sourceData, currValue, 0)
    counter = counter + 2

Next currValue

End Sub

Source data in A1:B3

Start layout

Upvotes: 0

Ekrem Gurdal
Ekrem Gurdal

Reputation: 1248

Maybe you can do it like this:

  1. Copy whole table to another sheet
  2. Create an id column for both table
  3. Give odd id number for the main table and give even id number for the copied table
  4. Copy and paste copied table to main table and sort main table by id and then remove id column and delete copied sheet.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

Here is one method which does not require fancy formulas or macros/VBA. Just copy the entire top block, in this case three rows, and then paste immediately underneath. Then sort by the first ID column, which will group the original row with its copy. Finally, generate a new ID sequence down all rows starting with one at the top. As a series of steps:

  1. Copy original data and paste underneath
  2. Sort new + copy data on the number ID column
  3. Generate a new ID column starting with 1 at the first row

Upvotes: 3

Related Questions