Excel - Repeat rows to a specific number of times
Solved/Closed
arhee
Posts
2
Registration date
Saturday February 8, 2014
Status
Member
Last seen
February 10, 2014
-
Feb 8, 2014 at 12:45 AM
daphtan Posts 2 Registration date Monday June 23, 2014 Status Member Last seen June 23, 2014 - Jun 23, 2014 at 05:48 AM
daphtan Posts 2 Registration date Monday June 23, 2014 Status Member Last seen June 23, 2014 - Jun 23, 2014 at 05:48 AM
Related:
- Excel repeat each row n times
- You have guessed too many times ✓ - WhatsApp Forum
- Excel create unique id for each row ✓ - Excel Forum
- Saints row 2 cheats - Guide
- Excel date format dd.mm.yyyy - Guide
- Excel duplicate rows n times ✓ - Office Software Forum
2 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jun 23, 2014 at 03:44 AM
Jun 23, 2014 at 03:44 AM
data is in sheet1
header 1 header 2 header 3
Peter male 3
james male 7
David male 4
try this macro and see sheet 2
header 1 header 2 header 3
Peter male 3
james male 7
David male 4
try this macro and see sheet 2
Sub test()
Dim rA As Range, cA As Range, dest As Range, j As Long, k As Long
Dim nname As String, sex As String
'data is in sheet1
With Worksheets("sheet1")
Set rA = Range(.Range("A2"), .Range("A2").End(xlDown))
For Each cA In rA
j = .Cells(cA.Row, "C")
nname = cA
sex = .Cells(cA.Row, "B")
With Worksheets("sheet2")
For k = 1 To j
Set dest = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
dest = nname
dest.Offset(0, 1) = sex
dest.Offset(0, 2) = j & "." & k
Next k
End With
Next cA
End With
End Sub
Jun 23, 2014 at 05:48 AM
but another query is, if I have multiple columns to be repeated based on the number in col x, do I change to something like this:
Sub test()
Dim rA As Range, cA As Range, dest As Range, j As Long, k As Long
Dim batch As String, location As String, code As String, color As String
'data is in sheet1
With Worksheets("sheet10")
Set rA = Range(.Range("A2"), .Range("A2").End(xlDown))
For Each cA In rA
j = .Cells(cA.Row, "E")
batch = cA
location = .Cells(cA.Row, "B")
code = .Cells(cA.Row, "C")
color = .Cells(cA.Row, "D")
With Worksheets("sheet11")
For k = 1 To j
Set dest = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
dest = batch
dest.Offset(0, 1) = location
dest.Offset(0, 2) = code
dest.Offset(0, 3) = color
dest.Offset(0, 4) = j & "." & k
Next k
End With
Next cA
End With
End Sub