VBA - Duplicate a value n number of times

December 2016



Issue


I'm trying to write a macro that allows to replicate value n times (input).
Let's say, I have a list
  • one
  • two
  • three



And I have to repeat it n (=4) times, the result should be in one column:
one  
one  
one  
one  
two  
two  
two  
two  
three  
three  
three  
three.  

Solution


If your data is range from A1 to A3

Try this macro:

Sub test() 
Dim r As String, n As Integer 
n = InputBox("type number of times to be replicated") 
 r = InputBox("the cells address whose values to be replciated, e.g A2    ") 
Range(Range(r).Offset(1, 0), Range(r).Offset(n - 1, 0)).EntireRow.Insert 
Range(r).Copy Range(Range(r).Offset(1, 0), Range(r).Offset(n - 1, 0)) 
End Sub 



Thanks to venkat1926 for this tip on the forum.

Related :

This document entitled « VBA - Duplicate a value n number of times » 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.