Macro to automatically merge cells

[roman209]

I currently have a table like this.

|This is to demonstrate what i want | | | | |
to do


I have rows like the above each | being a new cell.

I want the marco to:

1. Merge those 5 cells into 1.
2. Left align the text and enable wordwrap.
3. Resize the new merged cell to fit the text. - This is because merged cells do not automatically resize when they go over more than 1 line.

I want it to do this to the whole table. Except for the Header row of course.

 

[Leonard of Quirm]

roman,
Have you tried recording a macro to do this? When you do, all the code you need should be there for you to manipulate.
Try that and then come back if you have any problems.

 

[Zack Barresse]

I strongly recommend you stay away from merged cells and find another route to take. Excel is a spreadsheet and not a document editor; just trying to say some things are better done other ways.

 

[TrippyTom]

I too strongly suggest you find another way. Merged cells are the devil! They have consistently proven to be a source of corrupt files for me or files that crash. Besides, even merged cells don't wrap properly (they always get cut off vertically - if you test this, you will see).

Please show us more of your layout and someone will be able to give you a better solution.

Here's a possible workaround for what I think you want to happen:
1) Copy the entire text into an array
2) Figure out how wide your spreadsheet is (find the last column)
3) Estimate how many complete words fit that width (somehow using spaces as the key)
4) Insert a new row and paste the remaining text into that new row below the previous.
5) Continue until the end of the text array is reached

Unfortunately, I don't understand programming arrays in VBA, but if this is what you want, I'm sure someone will be able to help you figure it out.