Rows in an Excel worksheet can be repeated a specified number of times with user-defined functions. The Selection.insert command can be used to insert rows in an Excel sheet and there is no need to write the code from scratch as code snippets are freely available for use in worksheets. However, one must ensure that the user-defined function or subroutine to repeat rows a specified number of times is inserted in the right place.
How to repeat rows a specified number of times?
The situation in question here is how to repeat rows in a spreadsheet by a certain number of times specified in the cell present in that row i.e. from this table:
Column A Column B Peter 3 James 7 David 4
We want to produce this table:
Column A Column B Peter 3 Peter 3 Peter 3 James 7 James 7 James 7 James 7 James 7 James 7 James 7 David 4 David 4 David 4 David 4
What is the VBA code to repeat rows a specified number of times?
Users can try this code:
Sub CopyData() Dim lRow As Long Dim RepeatFactor As Variant lRow = 1 Do While (Cells(lRow, "A") <> "") RepeatFactor = Cells(lRow, "B") If ((RepeatFactor > 1) And IsNumeric(RepeatFactor)) Then Range(Cells(lRow, "A"), Cells(lRow, "B")).Copy Range(Cells(lRow + 1, "A"), Cells(lRow + RepeatFactor - 1, "B")).Select Selection.Insert Shift:=xlDown lRow = lRow + RepeatFactor - 1 End If lRow = lRow + 1 Loop End Sub
Need more help with Excel? Check out our forum!
- Duplicate rows based on cell value [solved] > Forum - Excel
- Excel - Repeat rows a specified number of times [solved] > Forum - Excel
- How to repeat rows by x number of times [solved] > Forum - Excel
- Excel - Repeat rows to a specific number of times [solved] > Forum - Excel
- Macro to copy/paste down X times (where X is the number of rows) [solved] > Forum - Excel
- How to use Excel color cell if formula
- What is the VBA code to select last sheet in workbook
- How to count names in Excel: formula, using COUNTIF
- How to use an IF statement to add 1 to total
- How to display multiple columns in a validation list
- How to insert file path in excel: cell, sheet
- Convert numbers to words in Excel: without VBA, formula
- How to copy a Macro into a blank cell
- How many IF statements can you nest in Excel
- How to apply a function to multiple sheets on Excel
- How to perform a partial cell match in Excel?
- How to change author name in Excel: spreadsheet, VBA
- How to insert GIF in Excel: 365, sheet, VBA
- How to take screenshot in Excel: shortcut, sheet, cell
- How to enable VBA in Excel: Mac, Office 365
- Copy data from one excel sheet to another: automatically
- Change date format in Excel: to dd/mm/yyyy, mm/dd/yyyy
- Fix arrows keys in Excel: Scroll lock, alternative method