AutoFit and Merged Cells

I had to do this once.

The following hack is how I manged to circumvent the problem... (hey I was in a hurry OK)

On the same row as the merged cells, I went way out to the right in a non-visible part of the worksheet and created a column that was the same width as all the merged cells combined.

I had the macro load both the single cell way out to the right and the visible merged cells with the same text. Since the single cell was set to resize itself and it was on the same row as the merged cells it always adjusted the height of my merged cells properly to display the text.

Yes, it's hacky. But it got the job done and I haven't bothered to revisit it since.

Aaron, hack as hack we must. I've had that same problem many times, and had given up using Wrap-text in merged cells. Your solution sounds perfect. Kicking myself for not having thought of it myself.

Aaron,
Doesn't feel like a hack to me. More of a cool, lateral thinking approach.
Thanks a bunch,
David

Hi,

Nice one Aroon

Here is an alternative solution which I picked up several years ago:

<PRE>
<FONT color=blue>Private <FONT color=blue>Sub </FONT></FONT>Worksheet_SelectionChange(<FONT color=blue>ByVal</FONT> Target<FONT color=blue> As</FONT> Range)

<FONT color=#ff0000>'Original created by Jim Rech
</FONT>


<FONT color=blue>Set </FONT>Target = Range("A1:A10")



<FONT color=blue>If </FONT>Intersect(ActiveCell, Target) Is<FONT color=blue> Nothing</FONT><FONT color=blue> Then </FONT><FONT color=blue>Exit Sub</FONT>



<FONT color=blue>Dim </FONT>snCurrRowH<FONT color=blue> As</FONT><FONT color=blue> Single</FONT>, MergedCellRgWidth<FONT color=blue> As</FONT><FONT color=blue> Single</FONT>

<FONT color=blue>Dim </FONT>CurrCell<FONT color=blue> As</FONT> Range

<FONT color=blue>Dim </FONT>ActiveCellWidth<FONT color=blue> As</FONT><FONT color=blue> Single</FONT>, PossNewRowHeight<FONT color=blue> As</FONT><FONT color=blue> Single</FONT>

<FONT color=blue>Dim </FONT>stCell<FONT color=blue> As</FONT><FONT color=blue> String</FONT>



<FONT color=blue>With </FONT>ActiveCell.MergeArea

<FONT color=blue>If </FONT>.Rows.Count = 1 And .WrapText =<FONT color=blue> True</FONT> Then

Application.ScreenUpdating =<FONT color=blue> False</FONT>

snCurrRowH = .RowHeight

ActiveCellWidth = ActiveCell.ColumnWidth

<FONT color=blue>For </FONT>Each CurrCell In Selection

MergedCellRgWidth = CurrCell.ColumnWidth + _

MergedCellRgWidth

Next

.MergeCells =<FONT color=blue> False</FONT>

.Cells(1).ColumnWidth = MergedCellRgWidth

.EntireRow.AutoFit

PossNewRowHeight = .RowHeight

.Cells(1).ColumnWidth = ActiveCellWidth

.MergeCells =<FONT color=blue> True</FONT>

.RowHeight = IIf(snCurrRowH > PossNewRowHeight, _

snCurrRowH, PossNewRowHeight)

<FONT color=blue>End If</FONT>

<FONT color=blue>End With</FONT>



<FONT color=blue>End Sub</FONT>
</PRE>

Do get it to work as it is above uncheck "Move selection after Enter".

Kind regards,
Dennis

I'm afraid I need a little more help with this. My issue seems to related to Excel column widths. If I have ten merged cells each with a width of 2 (as shown on the user interface) and I then create a column of width 20. The column is not as wide as the merged cells. I've experimented with this, with 2 merged cells versus a column sized at twice the width of one cell I can visually see a slight discrepancy and of course that grows with the number of cells.
David