Autofit text in merged cells

[lasseX]

Hello,

I have searched the internet after som code that deals with wrap text on merged cells. I have found some but nobody seams to work for me .

The backgroud: I have one row of cells:
Group1: A1:C1 are merged
Group2: F1:G1 are merged
Group3: I1:K1 are merged

I would like to autofit(wraptext) the text in the three groups just like if the cells where not merged. So if group 1 fits on one row and group 2 fits on two rows, two rows should be visable.

I hope anybody coudl help me!

Thanks

 

[SkipVought]

hi,

If you cannot fit your data per the MANUAL features outlined in Excel Spreadsheet HELP (Change the column width and row height), then it is HIGHLY UNLIKELY that you can accomplish this with VBA.

Merge cells are more of a bane than a blessing. The are many other pitfalls associated with using merge cells. My advice would be to avoid using merge cells if at all possible.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lasseX]

Hi Skip,

Thanks for the answer. I can not do it manually, I need to do it in automatic in some way.... and I need to have merged cells ...

Thanks

 

[SkipVought]

I can not do it manually...

Well THAT is a significant part of your answer!

I just read your spec in more detail, and what you propose is ABSOLUTELY NOT possible, specifically, since your merge cells are ALL in Row 1 as stated and you continue by stating, "So if group 1 fits on one row and group 2 fits on [red]two rows, two rows[/red] should be visable." Your MERGE is only defined for ONE ROW!

Now here's a possibility that has nothing to do with merged cells. Excel has a CAMERA feature. As the RANGE that is defined by the Camera (in your case, I would define ONE WRAP CELL that has the width of, for instance A:C on your target sheet) containing the text you want to see in Group 1, and then cut the PICTURE that the CAMERA produces and PASTE/Position it over A1:C1. As the text changes the height of the PICTURE will also change.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lasseX]

Thanks!! I will lock into that!

Do you know if it is possible to run c.EntireRow.AutoFit but not change anything in excel sheet ? To get the height without change anything.

Set c = Range("A1")
c.EntireRow.AutoFit
NewRwHt = c.RowHeight

 

[SkipVought]

Get the before and after row height and change it back

dim nRH as single, NewRwHt as single

with Range("A1").entirerow
   nRH = .rowheight
   .autofit
   NewRwHt = .rowheight
   .rowheight = nRH
end with

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lasseX]

 

[lasseX]

Do you know how to do this also

Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Address = "$F$9") Then
End if
End function

I have named the cell $F$9 to Test, how do I use this name instead ?

 

[SkipVought]

Please post new questions in a new thread.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]