Take the 2-minute tour ×

I need a macro which will copy each unique row within a spreadsheet and insert copied rows in the two rows directly beneath the original copied row and then repeat for each row there after.

It would be great if the macro could also input the following text strings - "(A)" in the original copied row, "(B)" in the second and "(C)" in the third.

The text string part isnt hugely important as I can always just use a concatenate formula if required.

Screenshot of what Im trying to achieve:


share|improve this question
What have you tried? –  assylias Mar 7 '12 at 15:22
I have tried using an Index combined with an MROUND formula but that doesnt seem to be working. Im literally out of ideas and it cant be a manual process as there are thousands of rows. –  user1254997 Mar 7 '12 at 15:55
Solved: C2: ="(A) "&INDEX(A:A,MROUND((ROW()+3)/3,1)) C3: ="(B) "&INDEX(A:A,MROUND((ROW()+3)/3,1)) C4: ="(C) "&INDEX(A:A,MROUND((ROW()+3)/3,1)) –  user1254997 Mar 7 '12 at 16:18
That works too - Your question stated that you needed a macro so I did not propose a formula. –  assylias Mar 7 '12 at 16:24
add comment

1 Answer

Assuming the data is in column A and you want the result in column C (as per your picture), this should work:

Public Sub doIt()

    Dim data As Variant
    Dim modifiedData As Variant
    Dim i As Long
    Dim j As Long

    data = ActiveSheet.UsedRange.Columns(1)
    ReDim modifiedData(1 To (UBound(data, 1) - 1) * 3 + 1, 1 To 1) As Variant

    modifiedData(1, 1) = data(1, 1) 'header

    j = 2
    For i = 2 To UBound(data, 1)
        modifiedData(j, 1) = "(A) - " & data(i, 1)
        modifiedData(j + 1, 1) = "(B) - " & data(i, 1)
        modifiedData(j + 2, 1) = "(C) - " & data(i, 1)
        j = j + 3
    Next i

    With ActiveSheet
        .Cells(1, 3).Resize(UBound(modifiedData, 1), 1) = modifiedData
    End With

End Sub
share|improve this answer
add comment

Your Answer


