CODEWITHSUNDEEP
excelvba
Tech Bro
Tech Bro

Reputation: 23

Repetition of Rows N times using Macro

I have a input as in Excel sheet

Date ISIN Issuer Type Maturit New Yield
20-May-2022 AB1234A Abcd Ltd Corporate 15-Jun-2022 Formula
20-May-2022 AB1234H GHIJ Ltd Corporate 31-May-2022 Formula

I want output as follows where date has to be incremented and repetition of rows after a set of rows , my input can be of any number of rows:

Date ISIN Issuer Type Maturit New Yield
20-May-2022 AB1234A Abcd Ltd Corporate 15-Jun-2022 Formula
20-May-2022 AB1234H GHIJ Ltd Corporate 31-May-2022 Formula
21-May-2022 AB1234A Abcd Ltd Corporate 15-Jun-2022 Formula
21-May-2022 AB1234H GHIJ Ltd Corporate 31-May-2022 Formula
22-May-2022 AB1234A Abcd Ltd Corporate 15-Jun-2022 Formula
22-May-2022 AB1234H GHIJ Ltd Corporate 31-May-2022 Formula

This is the Code:

Sub Repeat()
   Dim i As Long
   
   For i = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
      Rows(i).Copy
      Rows(i).Resize(Range("G" & i)).Insert
   Next i
End Sub

Upvotes: 0

Views: 85

Answers (1)

InjuredCoding
InjuredCoding

Reputation: 430

So not entirely certain of the use of this but... this should get you a long way to what you want (assumes dates are stored as dates not strings):

Sub RepeatResponse()
    finalRow = Range("A" & Rows.Count).End(xlUp).Row
    numofRows = finalRow - 1
    numOfCopies = 2

    Range(finalRow + 1 & ":" & finalRow + 1 + numOfCopies * numofRows - 1).EntireRow.Insert
    
    arrayHelper = Range("A2:F" & finalRow).FormulaR1C1Local
    For j = 1 To numOfCopies
        For i = 1 To numofRows
            arrayHelper(i, 1) = arrayHelper(i, 1) + 1
        Next i
        Range("A" & finalRow + 1 + (j - 1) * numofRows & ":F" & finalRow + 1 + (j) * numofRows - 1).FormulaR1C1Local = arrayHelper
    Next j
End Sub

Pulling this apart a little:

    finalRow = Range("A" & Rows.Count).End(xlUp).Row
    numofRows = finalRow - 1
    numOfCopies = 2

finalRow will be 3 in your input example with numofRows being 2 (excluding the title row) and in the example you had those rows copied twice (once for 21st once for 22nd).

    Range(finalRow + 1 & ":" & finalRow + 1 + numOfCopies * numofRows - 1).EntireRow.Insert

this selects the number of rows we need to insert to put the data in. if you don't need rows inserted (i.e. there is no data after the table or need for new rows for some other reason) then remove this line

this section sets up an array (for efficiency on larger runs and cycles the number of copies needed and the date changing

    arrayHelper = Range("A2:F" & finalRow).FormulaR1C1Local
    For j = 1 To numOfCopies
        For i = 1 To numofRows
            arrayHelper(i, 1) = arrayHelper(i, 1) + 1
        Next i
        Range("A" & finalRow + 1 + (j - 1) * numofRows & ":F" & finalRow + 1 + (j) * numofRows - 1).FormulaR1C1Local = arrayHelper
    Next j

Worth saying that if the number of copies is large then the array should be used more to increase efficiency. For the moment I believe this will be sufficient though

Upvotes: 1

Related Questions