Previous Topic Next Topic

EXCEL MACRO TO REPEAT AND INSERT ROWS

Solved/Closed
KU - May 6, 2010 at 05:16 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - 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

1 response

Answer 1 / 1
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 6, 2010 at 06:01 PM
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
0