How to Repeat Cell Value X Times in Excel (VBA Solutions)

Microsoft Excel is a powerful software. We can perform numerous operations on our datasets using Excel tools and features. There are many default Excel Functions that we can use to create formulas. Many institutions use Excel files to store important data. Sometimes, they need to repeat cell values and it’s easier to do that in Excel. This article will show you a easy methods to repeat cell value X times in Excel.

Code lovers will love this method. You can apply the VBA code to get cell values as many times as you want. So, go through the steps.

STEPS:

In the beginning, go to Developer ➤ Visual Basic.

• Consequently, the VBA window will pop out.
• Now, click Insert ➤ Module.
• The Module window will appear.

• Copy the following code and paste it there.

Sub RepeatValue()
Dim rg As Range
Dim ir As Range, org As Range
xTitleId = "arifulsh.com"
Set ir = Application.Selection
Set ir = Application.InputBox("Range :", xTitleId, ir.Address, Type:=8)
Set org = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
Set org = org.Range("A1")
For Each rg In ir.Rows
    xValue = rg.Range("A1").Value
    xNum = rg.Range("B1").Value
    org.Resize(xNum, 1).Value = xValue
    Set org = org.Offset(xNum, 0)
Next
End Sub

• Next, save the file and press F5 to run the code.

• As a result, a dialog box will pop out.

• Select the desired range and press OK.
• Again, another dialog box will appear.
• Select the cell where you’ll spill the data.
• Press OK.

Thus, you’ll see the cell values like it’s displayed below.