where text wraps in a cell, how can the row height be auto set?

[Stephen Sandor]

I have merged a number of cells and included text that wraps in the cell. Is
it possible to set the cell so that the height is automatically adjusted to
the height of the text?

[Greg Wilson]

Paste the following to the worksheet's code module. The code assumes that
each cell within A1:A10 is merged to adjacent columns as opposed to these
cells being merged - e.g. A1:D1 are merged, A2:D2 are merged, A3:D3 are
merged etc. Change the range reference to suit. Can be a single cell.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim r As Range, c As Range, cc As Range
Dim ma As Range

Set r = Range("A1:A10")
If Not Intersect(Target, r) Is Nothing Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
Application.ScreenUpdating = True
End If
End Sub

Alternatively, size the column width of a single cell in the same row to the
combined column widths of the merged range. Format the font, wraptext and
alignment exactly the same except change the font colour to be the same as
the background (to hide it). Enter a formula that references the active cell
of the merged range (e.g. "=C10"). Use the worksheet_change event to force
autofit of this cell. The merged cell range will then autofit along with it.
This assumes it is columns that are merged. Use the same logic if rows are
merged.

Regards,
Greg




"Stephen Sandor" wrote:

> I have merged a number of cells and included text that wraps in the cell. Is
> it possible to set the cell so that the height is automatically adjusted to
> the height of the text?

[Greg Wilson]

BTW, the code was adapted from an old Jim Rech post. Forgot to mention this.
Credit to him for the concept.

Greg

"Greg Wilson" wrote:

> Paste the following to the worksheet's code module. The code assumes that
> each cell within A1:A10 is merged to adjacent columns as opposed to these
> cells being merged - e.g. A1:D1 are merged, A2:D2 are merged, A3:D3 are
> merged etc. Change the range reference to suit. Can be a single cell.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim NewRwHt As Single
> Dim cWdth As Single, MrgeWdth As Single
> Dim r As Range, c As Range, cc As Range
> Dim ma As Range
>
> Set r = Range("A1:A10")
> If Not Intersect(Target, r) Is Nothing Then
> Set c = Target.Cells(1, 1)
> cWdth = c.ColumnWidth
> Set ma = c.MergeArea
> For Each cc In ma.Cells
> MrgeWdth = MrgeWdth + cc.ColumnWidth
> Next
> Application.ScreenUpdating = False
> ma.MergeCells = False
> c.ColumnWidth = MrgeWdth
> c.EntireRow.AutoFit
> NewRwHt = c.RowHeight
> c.ColumnWidth = cWdth
> ma.MergeCells = True
> ma.RowHeight = NewRwHt
> cWdth = 0: MrgeWdth = 0
> Application.ScreenUpdating = True
> End If
> End Sub
>
> Alternatively, size the column width of a single cell in the same row to the
> combined column widths of the merged range. Format the font, wraptext and
> alignment exactly the same except change the font colour to be the same as
> the background (to hide it). Enter a formula that references the active cell
> of the merged range (e.g. "=C10"). Use the worksheet_change event to force
> autofit of this cell. The merged cell range will then autofit along with it.
> This assumes it is columns that are merged. Use the same logic if rows are
> merged.
>
> Regards,
> Greg
>
>
>
>
> "Stephen Sandor" wrote:
>
> > I have merged a number of cells and included text that wraps in the cell. Is
> > it possible to set the cell so that the height is automatically adjusted to
> > the height of the text?

[ExcelQuestion]

Hi Greg,
This is a good post and thanks to Jim Rech also. When this code triggers, there is a blank line at the top row....which I'd still have to manually use the autofit function to get the text to fit within the cell without visible extra lines. How do you tweak this so that the extra line at the top doesn't show?

Thanks,
Ricky

BTW, the code was adapted from an old Jim Rech post. Forgot to mention this.
Credit to him for the concept.

Greg

"Greg Wilson" wrote:

> Paste the following to the worksheet's code module. The code assumes that
> each cell within A1:A10 is merged to adjacent columns as opposed to these
> cells being merged - e.g. A1:D1 are merged, A2:D2 are merged, A3:D3 are
> merged etc. Change the range reference to suit. Can be a single cell.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim NewRwHt As Single
> Dim cWdth As Single, MrgeWdth As Single
> Dim r As Range, c As Range, cc As Range
> Dim ma As Range
>
> Set r = Range("A1:A10")
> If Not Intersect(Target, r) Is Nothing Then
> Set c = Target.Cells(1, 1)
> cWdth = c.ColumnWidth
> Set ma = c.MergeArea
> For Each cc In ma.Cells
> MrgeWdth = MrgeWdth + cc.ColumnWidth
> Next
> Application.ScreenUpdating = False
> ma.MergeCells = False
> c.ColumnWidth = MrgeWdth
> c.EntireRow.AutoFit
> NewRwHt = c.RowHeight
> c.ColumnWidth = cWdth
> ma.MergeCells = True
> ma.RowHeight = NewRwHt
> cWdth = 0: MrgeWdth = 0
> Application.ScreenUpdating = True
> End If
> End Sub
>
> Alternatively, size the column width of a single cell in the same row to the
> combined column widths of the merged range. Format the font, wraptext and
> alignment exactly the same except change the font colour to be the same as
> the background (to hide it). Enter a formula that references the active cell
> of the merged range (e.g. "=C10"). Use the worksheet_change event to force
> autofit of this cell. The merged cell range will then autofit along with it.
> This assumes it is columns that are merged. Use the same logic if rows are
> merged.
>
> Regards,
> Greg
>
>
>
>
> "Stephen Sandor" wrote:
>
> > I have merged a number of cells and included text that wraps in the cell. Is
> > it possible to set the cell so that the height is automatically adjusted to
> > the height of the text?