0

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.

  • @Zarina Akhtar please see answer – Tony Chivers Nov 13 '18 at 8:35
1

With data in column A, in B1 enter:

=IF(ROW()>COUNTA(A:A)*12,"",INDEX(A:A,MOD(ROW()-1,COUNTA(A:A))+1))

and copy downwards:

enter image description here

Column B will automatically adjust if column A changes.

  • Works just aswell without the need for VBA code! – Tony Chivers Nov 14 '18 at 13:54
  • @TonyChivers Yes.............thanks for the feedback..............as you see, it is easy to create a simple sequential index that is repeatable. – Gary's Student Nov 14 '18 at 13:58
1

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
  • 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 Nov 13 '18 at 10:48
  • 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 Nov 13 '18 at 11:02

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.