Issue with data in cell getting cut off 2

[Stoffel24]

Hi
I have a problem with data in a merged cell being cut off. I have a list of names (Scott, George, Susie, Arthur) in cells C2 to D2 (ie cells are merged). The problem is that the data is clipped short and I cannot do a columns.autofit as the columns are merged and this doesn't seem to work.

My code is as follows:
Cells.Select
With Selection
.Columns.AutoFit
End With

Is there a neat way to get all the data to fit into the cells?
Just to provide some more info, the data is initially in one cell and I later merge it so I was thinking of determining the width of the autofitted single cell and later doing an if statement
If Merged cells.totalcolumnwidth < originalcolumnwidth then
Make the merged cells wider blah blah
End if
Very clumsy.

Ideas [Sadeyes]

 

[bryanbayfield]

If you use a font size of Arial 10 then 17 characters roughly equals a width of 13 points.

>> Get the length of the string in your merged cell, then set the width of each of the two columns that intersect with the cell to the string length * 13/34.

You'll need the ColumnWidth property of the Range object. It's not perfect but I don't know how else to do it. The ratio will obviously alter if you use a different font (/size).

 

[RobBroekhuis]

One way to do it would be to put the value of your merged cell into an unused column of your spreadsheet, tell Excel to autofit that column, and then use the resulting column width as the sum of columns C and D. You'd still need to decide how to split up the widths between the two columns.
Rob

 

[bandit600]

Would wrapping the text do?

If so try

Worksheets(&quot;Sheet1&quot.Range(&quot;c2:d2&quot.WrapText = True

bandit600

 

[Stoffel24]

Thanks a lot all of you. I actually ended up doing pretty much what Rob suggested - ie did an autofit in an unmerged cell and worked out the width of that cell. Then ensured that the width of the merged cells was equal to that.
Does anyone know how I can determine the width of all the merged cells without doing something like...
For Each Col In Selection.Columns
ColTot = ColTot + Col.Columnwidth
Next

I know it isn't a lot of lines but it will take a while if there are many columns and if I could do this without looping, that would be great.

Thanks [Thumbsup]

 

[Loomah]

Do you mean the total width of your merged cells? If so

[a1].Select
Debug.Print Selection.Width

should do the trick. In this case I have a1 to A3 merged. Not sure how to reference the range without selecting. Referencing A1 just gives the width of column A. There must be a way to avoid selecting your merged cells but I haven't the time to work it out!!

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[RobBroekhuis]

Do like Loomah suggests, just don't use the merged cell itself - use unmerged cells above or below it, e.g.
range(&quot;A1:C1&quot.width

Rob