Related:
- Excel repeat rows a specified number of times vba
- Excel - repeat rows a specified number of times - Best answers
- Excel - Repeat rows a specified number of times ✓ - Forum - Excel
- How to Repeat Rows a Specified Number of Times on Excel - How-To - Excel
- How to repeat rows by x number of times ✓ - Forum - Excel
- Copy and insert rows and number of times ✓ - Forum - Excel
- Macro to copy/paste down X times (where X is the number of rows) ✓ - Forum - Excel
2 replies
venkat1926
- Posts
- 1862
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- July 30, 2015
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
venkat1926
- Posts
- 1862
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- July 30, 2015
suppose row 1 is header row
row 2, 3 and 4 has data
hdng1 hdng2
Peter 3
James 7
David 4
try this macro
row 2, 3 and 4 has data
hdng1 hdng2
Peter 3
James 7
David 4
try this macro
Sub test()
Dim r As Range, c As Range, j As Integer, dest As Range, k As Integer
Set r = Range(Range("A2"), Range("A2").End(xlDown))
For Each c In r
j = c.Offset(0, 1)
Set dest = Cells(Rows.Count, "C").End(xlUp).Offset(1, 0)
For k = 1 To j
dest.Offset(k - 1, 0) = c
dest.Offset(k - 1, 1) = k
Next k
Next c
End Sub
daphtan
- Posts
- 2
- Registration date
- Monday June 23, 2014
- Status
- Member
- Last seen
- June 23, 2014
Hi, my scenario is:
data
col 1 col 2 col 3
header 1 header 2 header 3
Peter male 3
James male 7
David male 4
output
col 1 col 2 col 3 col 4
Peter male 3 1
Peter male 3 2
Peter male 3 3
James male 7 1
James male 7 2
James male 7 3
James male 7 4
James male 7 5
James male 7 6
James male 7 7
David male 4 1
David male 4 2
David male 4 3
David male 4 4
pls help~ thanks!
data
col 1 col 2 col 3
header 1 header 2 header 3
Peter male 3
James male 7
David male 4
output
col 1 col 2 col 3 col 4
Peter male 3 1
Peter male 3 2
Peter male 3 3
James male 7 1
James male 7 2
James male 7 3
James male 7 4
James male 7 5
James male 7 6
James male 7 7
David male 4 1
David male 4 2
David male 4 3
David male 4 4
pls help~ thanks!
Subscribe To Our Newsletter!
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