Concatenate a column of texts into chunks of 10 with VBA

Hi Everyone,

I basically have little to no knowledge of VBA. I have a list of hundreds of names in column A starting in Cell A3 in which i want to concatenate in chunks of 10 separated by ", " in-between each cell as well as add a hyphen " - " and a specific text declared in Cell A1.

I have to repeat this task frequently with different numbers of text so I would like for the code to run until an empty cell is detected after Cell A3. I would like to output to be in column C

This is an example of what I want done:

DATA IN COLUMN A
A1: List of Names
A2:
A3: Lisa
A4: John
A5: Mary
A6: Joe
A7: Bill
A8: Annie
A9: Jane
A10: Cindy
A11: Larry
A12: Aaron
A13: Nate
A14: Jess
A15: Phil

OUTPUT IN COLUMN C
C1: Lisa, John, Mary, Joe, Bill, Annie, Jane, Cindy, Larry, Aaron - List of Names
C2: Nate, Jess, Phil - List of Names



I know I can use the excel formula =CONCATENATE(A3,", ",A4,", ",A5,", ",A6,", ",A7,", ",A8,", ",A9,", ",A10,", ",A11,", ",A12," - ",A1) for the first output, but this will be very time consuming and I don't know how long the list of names will be in the future. Any help would be appreciated!

This is great! Thank you. One more question... instead of having " - List of Names" embedded in the code, can I have it as " - " & A1.

Basically, The text in A1 will change every time that needs to be inputted after the hyphen so I would prefer not to have "List of Names" embedded in the code.

Originally Posted by annabellesays

This is great! Thank you. One more question... instead of having " - List of Names" embedded in the code, can I have it as " - " & A1.

Basically, The text in A1 will change every time that needs to be inputted after the hyphen so I would prefer not to have "List of Names" embedded in the code.

Nevermind I got it! I changed it to " - " & Range("A1")

Thanks again!