Excel - Row widths & merged cells

[krehkop]

I have 3 cells (in columns a,b & c) that are merged. Text is entered into this merged cell. Why can't I highlight the row, double click one of the lines above or below the row number and have the row automatically format correctly to a width that shows all text? If I center across selection instead of merging this will work, however, the format is wrong b/c everything's centered.

Does anyone know why this is or a better way?

Thanks!

 

[KenMillard]

I don't think there is any way to "automatically" do this but it can be done with code.

It entails unmerging the cells, doing an autoheight or autowidth, getting the width or height of the single cell, re-merging the cells then setting the width or height based on what the unmerged cell autosized to.

 

[krehkop]

Thanks Ken, I will play around w/ a macro based on that info...

 

[KenMillard]

I mis-stated the solution.


get the full width of the merged range
un merge the cells
set the single cell width to what the merged width was
autosize the row
return the first cell width to it's original width
re-merge the cells


It sounds pretty easy but there may be issues with pixels, points and characters.

 

[KenMillard]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim PartialWidth As Long
Dim FullWidth As Long
Dim CellCount As Long
Dim X As Long
Dim NewHeight As Long


'Copying and pasting causes an error trying to retrieve the merge area
If Application.CutCopyMode Then Exit Sub


    If Target.MergeCells Then
        PartialWidth = Target.MergeArea.Cells(1).ColumnWidth
        CellCount = Target.MergeArea.Cells.Count
        For X = 1 To CellCount
            FullWidth = FullWidth + Target.MergeArea.Cells(1, X).ColumnWidth
   
        Next
        Target.MergeCells = False
        Target.EntireColumn.ColumnWidth = FullWidth
        Target.EntireRow.AutoFit
        NewHeight = Target.RowHeight
        Target.ColumnWidth = PartialWidth
        Target.Resize(, CellCount).MergeCells = True
        Target.EntireRow.RowHeight = NewHeight
    End If
    

End Sub

 

[Oaktree]

You might have a look through this thread too:

http://www.mrexcel.com/board2/viewtopic.php?t=126936

 

[krehkop]

Thanks Ken and Oaktree...excellent!

Oaktree, Barry Katcher's picture gave me a good laugh this morning...I needed that!