Repeating a list of cells in excel X number of times
up vote
1
down vote
favorite
I have a list of names in excel,
For example
Lizzy
William
Kate
Charles
I would like to use some VBA code (or other method to repeat this 12 times).
I will be doing this over and over again and sometimes my list of names may include less or more.
Has anyone got any direction for me as I am really stuck.
The output I would like in this specific case is,
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
What I have tried:
I have found a method using formulas, see, https://www.excel-bytes.com/how-to-repeat-a-range-of-items-multiple-times-in-excel/
However the problem with this is the indexing the initial list as my list will change.
Link to question put on hold: Copy and paste a list of names in excel 12 times
This question was originally asked by another user and put on hold so
I couldn't answer. I have a solution so I have reasked the question
and I will provide an answer and tag the original asker.
microsoft-excel vba
New contributor
add a comment |
up vote
1
down vote
favorite
I have a list of names in excel,
For example
Lizzy
William
Kate
Charles
I would like to use some VBA code (or other method to repeat this 12 times).
I will be doing this over and over again and sometimes my list of names may include less or more.
Has anyone got any direction for me as I am really stuck.
The output I would like in this specific case is,
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
What I have tried:
I have found a method using formulas, see, https://www.excel-bytes.com/how-to-repeat-a-range-of-items-multiple-times-in-excel/
However the problem with this is the indexing the initial list as my list will change.
Link to question put on hold: Copy and paste a list of names in excel 12 times
This question was originally asked by another user and put on hold so
I couldn't answer. I have a solution so I have reasked the question
and I will provide an answer and tag the original asker.
microsoft-excel vba
New contributor
@Zarina Akhtar please see answer
– Tony Chivers
4 hours ago
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
I have a list of names in excel,
For example
Lizzy
William
Kate
Charles
I would like to use some VBA code (or other method to repeat this 12 times).
I will be doing this over and over again and sometimes my list of names may include less or more.
Has anyone got any direction for me as I am really stuck.
The output I would like in this specific case is,
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
What I have tried:
I have found a method using formulas, see, https://www.excel-bytes.com/how-to-repeat-a-range-of-items-multiple-times-in-excel/
However the problem with this is the indexing the initial list as my list will change.
Link to question put on hold: Copy and paste a list of names in excel 12 times
This question was originally asked by another user and put on hold so
I couldn't answer. I have a solution so I have reasked the question
and I will provide an answer and tag the original asker.
microsoft-excel vba
New contributor
I have a list of names in excel,
For example
Lizzy
William
Kate
Charles
I would like to use some VBA code (or other method to repeat this 12 times).
I will be doing this over and over again and sometimes my list of names may include less or more.
Has anyone got any direction for me as I am really stuck.
The output I would like in this specific case is,
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
Lizzy
William
Kate
Charles
What I have tried:
I have found a method using formulas, see, https://www.excel-bytes.com/how-to-repeat-a-range-of-items-multiple-times-in-excel/
However the problem with this is the indexing the initial list as my list will change.
Link to question put on hold: Copy and paste a list of names in excel 12 times
This question was originally asked by another user and put on hold so
I couldn't answer. I have a solution so I have reasked the question
and I will provide an answer and tag the original asker.
microsoft-excel vba
microsoft-excel vba
New contributor
New contributor
New contributor
asked 4 hours ago
Tony Chivers
184
184
New contributor
New contributor
@Zarina Akhtar please see answer
– Tony Chivers
4 hours ago
add a comment |
@Zarina Akhtar please see answer
– Tony Chivers
4 hours ago
@Zarina Akhtar please see answer
– Tony Chivers
4 hours ago
@Zarina Akhtar please see answer
– Tony Chivers
4 hours ago
add a comment |
1 Answer
1
active
oldest
votes
up vote
1
down vote
For any list of names in the first column of an excel spreadsheet, this VBA code will repeat based on the entry in cell C2, therefore, if you want the list repeated 10 times and there are 10 names in the original list, insert a 110 in the original list.
Option Explicit
Sub LRow()
Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1") '<=== Edit Sheet Name
Dim LRow As Long
LRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
ws.Range("A1:A" & LRow).Select
End Sub
Is the first but of code that you need and it essentially highlights anything in the A column.
The second part of the code drags down the list so that it is repeated.
Add them both in and run the second as a macro.
I hope this helps.
Sub Master()
'
' Master Macro
'
'
LRow
Selection.AutoFill Destination:=Range("A1:A" & Range("C2").Value), Type:=xlFillDefault
Range("A1:A" & Range("C2").Value).Select
Range("B1").Select
End Sub
New contributor
1
However stupid it may sound, I have to ask: Is there anything wrong with typing in the list, then dragging down (lower right corner repetition mark) up to 12 times? You may end up short or long, but no code is needed, and it's fast.
– DroidW
2 hours ago
1
Yes, I see your point, but I think the original asker want to have a foolproof way that could be used in other automation processes. "I will be doing this over and over again and sometimes my list of names may include less or more."
– Tony Chivers
2 hours ago
1
Crystal clear, thank you.
– DroidW
1 hour ago
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
For any list of names in the first column of an excel spreadsheet, this VBA code will repeat based on the entry in cell C2, therefore, if you want the list repeated 10 times and there are 10 names in the original list, insert a 110 in the original list.
Option Explicit
Sub LRow()
Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1") '<=== Edit Sheet Name
Dim LRow As Long
LRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
ws.Range("A1:A" & LRow).Select
End Sub
Is the first but of code that you need and it essentially highlights anything in the A column.
The second part of the code drags down the list so that it is repeated.
Add them both in and run the second as a macro.
I hope this helps.
Sub Master()
'
' Master Macro
'
'
LRow
Selection.AutoFill Destination:=Range("A1:A" & Range("C2").Value), Type:=xlFillDefault
Range("A1:A" & Range("C2").Value).Select
Range("B1").Select
End Sub
New contributor
1
However stupid it may sound, I have to ask: Is there anything wrong with typing in the list, then dragging down (lower right corner repetition mark) up to 12 times? You may end up short or long, but no code is needed, and it's fast.
– DroidW
2 hours ago
1
Yes, I see your point, but I think the original asker want to have a foolproof way that could be used in other automation processes. "I will be doing this over and over again and sometimes my list of names may include less or more."
– Tony Chivers
2 hours ago
1
Crystal clear, thank you.
– DroidW
1 hour ago
add a comment |
up vote
1
down vote
For any list of names in the first column of an excel spreadsheet, this VBA code will repeat based on the entry in cell C2, therefore, if you want the list repeated 10 times and there are 10 names in the original list, insert a 110 in the original list.
Option Explicit
Sub LRow()
Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1") '<=== Edit Sheet Name
Dim LRow As Long
LRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
ws.Range("A1:A" & LRow).Select
End Sub
Is the first but of code that you need and it essentially highlights anything in the A column.
The second part of the code drags down the list so that it is repeated.
Add them both in and run the second as a macro.
I hope this helps.
Sub Master()
'
' Master Macro
'
'
LRow
Selection.AutoFill Destination:=Range("A1:A" & Range("C2").Value), Type:=xlFillDefault
Range("A1:A" & Range("C2").Value).Select
Range("B1").Select
End Sub
New contributor
1
However stupid it may sound, I have to ask: Is there anything wrong with typing in the list, then dragging down (lower right corner repetition mark) up to 12 times? You may end up short or long, but no code is needed, and it's fast.
– DroidW
2 hours ago
1
Yes, I see your point, but I think the original asker want to have a foolproof way that could be used in other automation processes. "I will be doing this over and over again and sometimes my list of names may include less or more."
– Tony Chivers
2 hours ago
1
Crystal clear, thank you.
– DroidW
1 hour ago
add a comment |
up vote
1
down vote
up vote
1
down vote
For any list of names in the first column of an excel spreadsheet, this VBA code will repeat based on the entry in cell C2, therefore, if you want the list repeated 10 times and there are 10 names in the original list, insert a 110 in the original list.
Option Explicit
Sub LRow()
Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1") '<=== Edit Sheet Name
Dim LRow As Long
LRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
ws.Range("A1:A" & LRow).Select
End Sub
Is the first but of code that you need and it essentially highlights anything in the A column.
The second part of the code drags down the list so that it is repeated.
Add them both in and run the second as a macro.
I hope this helps.
Sub Master()
'
' Master Macro
'
'
LRow
Selection.AutoFill Destination:=Range("A1:A" & Range("C2").Value), Type:=xlFillDefault
Range("A1:A" & Range("C2").Value).Select
Range("B1").Select
End Sub
New contributor
For any list of names in the first column of an excel spreadsheet, this VBA code will repeat based on the entry in cell C2, therefore, if you want the list repeated 10 times and there are 10 names in the original list, insert a 110 in the original list.
Option Explicit
Sub LRow()
Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1") '<=== Edit Sheet Name
Dim LRow As Long
LRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
ws.Range("A1:A" & LRow).Select
End Sub
Is the first but of code that you need and it essentially highlights anything in the A column.
The second part of the code drags down the list so that it is repeated.
Add them both in and run the second as a macro.
I hope this helps.
Sub Master()
'
' Master Macro
'
'
LRow
Selection.AutoFill Destination:=Range("A1:A" & Range("C2").Value), Type:=xlFillDefault
Range("A1:A" & Range("C2").Value).Select
Range("B1").Select
End Sub
New contributor
edited 2 hours ago
New contributor
answered 4 hours ago
Tony Chivers
184
184
New contributor
New contributor
1
However stupid it may sound, I have to ask: Is there anything wrong with typing in the list, then dragging down (lower right corner repetition mark) up to 12 times? You may end up short or long, but no code is needed, and it's fast.
– DroidW
2 hours ago
1
Yes, I see your point, but I think the original asker want to have a foolproof way that could be used in other automation processes. "I will be doing this over and over again and sometimes my list of names may include less or more."
– Tony Chivers
2 hours ago
1
Crystal clear, thank you.
– DroidW
1 hour ago
add a comment |
1
However stupid it may sound, I have to ask: Is there anything wrong with typing in the list, then dragging down (lower right corner repetition mark) up to 12 times? You may end up short or long, but no code is needed, and it's fast.
– DroidW
2 hours ago
1
Yes, I see your point, but I think the original asker want to have a foolproof way that could be used in other automation processes. "I will be doing this over and over again and sometimes my list of names may include less or more."
– Tony Chivers
2 hours ago
1
Crystal clear, thank you.
– DroidW
1 hour ago
1
1
However stupid it may sound, I have to ask: Is there anything wrong with typing in the list, then dragging down (lower right corner repetition mark) up to 12 times? You may end up short or long, but no code is needed, and it's fast.
– DroidW
2 hours ago
However stupid it may sound, I have to ask: Is there anything wrong with typing in the list, then dragging down (lower right corner repetition mark) up to 12 times? You may end up short or long, but no code is needed, and it's fast.
– DroidW
2 hours ago
1
1
Yes, I see your point, but I think the original asker want to have a foolproof way that could be used in other automation processes. "I will be doing this over and over again and sometimes my list of names may include less or more."
– Tony Chivers
2 hours ago
Yes, I see your point, but I think the original asker want to have a foolproof way that could be used in other automation processes. "I will be doing this over and over again and sometimes my list of names may include less or more."
– Tony Chivers
2 hours ago
1
1
Crystal clear, thank you.
– DroidW
1 hour ago
Crystal clear, thank you.
– DroidW
1 hour ago
add a comment |
Tony Chivers is a new contributor. Be nice, and check out our Code of Conduct.
Tony Chivers is a new contributor. Be nice, and check out our Code of Conduct.
Tony Chivers is a new contributor. Be nice, and check out our Code of Conduct.
Tony Chivers is a new contributor. Be nice, and check out our Code of Conduct.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1374954%2frepeating-a-list-of-cells-in-excel-x-number-of-times%23new-answer', 'question_page');
}
);
Post as a guest
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
@Zarina Akhtar please see answer
– Tony Chivers
4 hours ago