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.
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.