Macro to merge cells based on the number of items in a list

Hi all,

I'm in the process of creating an archiving function for a spreadsheet and I'm a bit stuck. I've created a button and macro to record information in specific cells into a table, and each time the button is pressed the entries are recorded on the next row down - this part works fine.

What I can't figure out how to do is to get items of a user populated list (could be 1 item, could be 20...) into the table and then auto merge & center the cells to the left.

It's a bit difficult to explain so I've attached the spreadsheet - on the Archive tab, the table from rows B:F is where I've got to, and the look of the table from rows N:R is what I'm trying to achieve.

Any help / suggestions would be greatly appreciated!!

Thanks,
-Adam

Re: Macro to merge cells based on the number of items in a list

Hi, either the list items all in one cell, or the list items in separate cells with the cells in columns to the left being merged down, i.e. if there ends up being 5 items in the list, there should be 5 cells merged down in the columns to the left.

Apologies for the poor description!

Re: Macro to merge cells based on the number of items in a list

To try and explain better, if the following cells had text:

Cell A2: Hello
Cell A3: Everyone

I'm looking for a macro to take those and make

Cell C4: Hello, Everyone