Macro to automatically merge cells

roman209

New Member
Joined
Oct 11, 2005
Messages
27
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.
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Leonard of Quirm

Board Regular
Joined
May 19, 2005
Messages
180
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

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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

Well-known Member
Joined
Nov 16, 2004
Messages
587
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,910
Messages
5,574,987
Members
412,632
Latest member
robertmwaring2
Top