How can I repeat 100+ rows In Excel

October 30, 2010 at 10:40:56
Specs: Windows XP
I have 100 rows, containing unique data in each row. I want to be able to repeat each row and its cell content 10 times. This should give me a worksheet of 1000 rows. every 10 rows should show the same cell content.

Hope you can help, as this would save me so much processing time.

See More: How can I repeat 100+ rows In Excel

Report •

October 30, 2010 at 11:02:54
Simplest way I can think of is to Highlight your 100 rows and Copy & Paste 10 times.
It would probably take longer to create and enter a formula or VBA code.


Report •

October 30, 2010 at 19:41:31
Do you mean that you want to repeat Row 1 ten times, then repeat Row 2 ten times, etc?

If so, then this macro will copy each Row from Sheet 1 and paste it 10 times into Sheet2

Option Explicit
Sub TenRows()
Dim pasteRow, srcRow, dstRow As Integer
'Initialize "paste location" variable
  pasteRow = 1
'Loop through 100 source rows
  For srcRow = 1 To 100
'Copy/paste each row 10 times
    For dstRow = pasteRow To pasteRow + 9
      Sheets(1).Rows(srcRow).EntireRow.Copy _
'Increment "paste location" variable by 10
     pasteRow = pasteRow + 10
End Sub

Report •

October 31, 2010 at 05:03:27
Thank you for this Macro, this is perfect, i'll use it as part of a process for creating mutiple move labels to relocate people from one desk location to another.

I will tweak the macro a little, I receive spreadsheets that contain either 10 to 1000 rows of information. I use MS Word to mail merge the spreadsheet contents for the creation of move labels, this macro you have provided will assist greatly in structuring the spreadsheets ready for a mail merge.

I can now even group departments easier.


Once again thank you.

Report •

Related Solutions

October 31, 2010 at 05:09:29
Hi my colleague uses this method and it pains me to see how long it can take him at times to process the spreadsheets. I am a very lazy kind of guy and like to identify effective shortcuts to completing my work in the shortest possible time.

I call this worksmart, not hard. they don't pay me enough.

But thank you for your suggestion!


Report •

Ask Question