EXCEL MACRO TO REPEAT AND INSERT ROWSSolved/Closed

Question

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

rizvisa1 · Answer

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

EXCEL MACRO TO REPEAT AND INSERT ROWS

1 response

Similar discussions

Subscribe To Our Newsletter!