EXCEL MACRO TO REPEAT AND INSERT ROWS [Solved/Closed]

- May 6, 2010 at 05:16 PM - Latest reply:
Posts
4481
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
- May 6, 2010 at 06:01 PM
I am trying to write a macro that will insert rows as follows:
Col 1 Col2 Col3
1 S 456
2 A 222
3 M 952

So i want the macro to copy and insert as follows (number continues to add, while the other columns repeat in the same order as a group).

Col 1 Col2 Col3
1 S 456
2 A 222
3 M 952
4 S 456
5 A 222
6 M 952
See more 

1 reply

Posts
4481
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
- May 6, 2010 at 06:01 PM
0
Thank you
This is based on assumption that
1. Data to be copied start from row 2
2. The series is in column A
3. Row 1 has the max used column

Sub RepeatAndRinse()

Dim lMaxRows As Long
Dim iMaxCols As Long
Dim iStartAtRow As Integer

    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
    iMaxCols = Cells(1, Columns.Count).End(xlToLeft).Column
    iStartAtRow = 2
    
    Range(Cells(iStartAtRow, 2), Cells(lMaxRows, iMaxCols)).Copy
    Cells(lMaxRows + 1, 2).Select
    ActiveSheet.Paste
    
    Cells(lMaxRows, 1).Select
    Selection.AutoFill Destination:=Range("A" & lMaxRows & ":A" & lMaxRows * 2 + (1 - iStartAtRow)), Type:=xlFillSeries
   
End Sub