VBA - Duplicate a value n number of times

March 2017



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


Published by aakai1056.
This document, titled "VBA - Duplicate a value n number of times," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).