How to duplicate rows

[btamulis]

Interesting scenario -

I have a spreadsheet with one column - 35,000 unique item numbers

Example:

item a
item b
item c
item d
item e

I need to duplicate each row - making a spreadshhet with one column 70,000 rows as such:

item a
item a
item b
item b
item c
item c
item d
item d
item e
item e

I tried a formula - couldn't come up with anything - looked for some kind of merge type functionality - did find anything........

Any guidance would be appreciated........ <!-- / message --><!-- BEGIN TEMPLATE: ad_showthread_firstpost_sig --><!-- END TEMPLATE: ad_showthread_firstpost_sig -->

 

[ChrisM]

Copy and paste a duplicate column underneath the original, then sort the entire data set.

 

[VoG]

Try

Sub InsRows()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For i = LR To 1 Step -1
    Rows(i).Copy
    Rows(i).Insert
Next i
Application.ScreenUpdating = True
End Sub

 

[btamulis]

Peter - how do i change my worksheet to exceed the 65,000+ rows - the code errored out - I think because my rows = 38,365 which when doubled goes to 76,730.........I am on excel 2007 - so I do have capability

 

[VoG]

Make sure to save the file in one of the 2007 formats - xlsx or xlsm - first.

 

[btamulis]

Worked...........brilliant.........thanks everyone!