Excel - A macro to repeat rows and fill series

December 2016




Issue


This is variation of a question that has been answered previously. Does anybody know how I can repeat rows in a spreadsheet by n number of times specified in a cell in that row AND then fill a series - so that:
Column A Column B
Peter 3
James 7
David 4


Will then produce this table:
Column A Column B Column C
Peter 3 1
Peter 3 2
Peter 3 3
James 7 1
James 7 2
James 7 3
James 7 4
James 7 5
James 7 6
James 7 7

I'd appreciate any help I can get with this.

Solution


Sub repeat()  
Dim n As Integer  
Dim counter As Integer  

Let n = 1  
Let counter = 1  
While Cells(n, 1) <> ""  
    For x = 1 To Cells(n, 2)  
        Cells(counter, 10) = Cells(n, 1)  
        Cells(counter, 11) = Cells(n, 2)  
        Cells(counter, 12) = x  
        Let counter = counter + 1  
    Next x  
n = n + 1  
Wend  

End Sub  

Note that


Solved by RayH on the forum.

Related :

This document entitled « Excel - A macro to repeat rows and fill series » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.