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 •


#1
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.

MIKE

http://www.skeptic.com/


Report •

#2
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 _
        Destination:=Sheets(2).Rows(dstRow)
    Next
'Increment "paste location" variable by 10
     pasteRow = pasteRow + 10
  Next
End Sub


Report •

#3
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.

Hans6000

Once again thank you.


Report •

Related Solutions

#4
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!

Hans6000


Report •


Ask Question