How to use code to insert below and copy the row as many times as the cell contains
Category: [Excel] Demo Available
How to use code to insert below and copy the row as many times as the cell contains
Requirement:
The sheet contains data in A-BD columns and hundreds of lines down.
The user needs to duplicate down the lines in which the column E>1 as many times as it has a value, minus 1.
For example, row 10, column E=3, I need to add 2 rows below 10 and copy the contents of row 10 to them (there will be 3 same rows).
Solution:
Code:
Sub InserRows()
Application.ScreenUpdating = False
Dim LastRow As Long
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Dim x As Long
For x = LastRow To 2 Step -1
If Cells(x, 5) > 1 Then
Rows(x).EntireRow.Copy
Cells(x + 1, 1).Resize(Cells(x, 5).Value - 1).Insert
End If
Next x
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by Mumps.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use VBA code to transpose any copy values X number of times |
| How to use code to insert below and copy the row as many times as the cell contains |
| How to find and insert rows for missing timestamps, remove rows for unwanted timestamps |
| How to calculate overtime on Timesheet after 10 hours per day AND/OR 44 hours per week |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.