software

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?

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. A11 are merged, A22 are merged, A33 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(quot;A1:A10quot;)
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. quot;=C10quot;). 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

quot;Stephen Sandorquot; wrote:

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

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

Greg

quot;Greg Wilsonquot; wrote:

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

If you are linking a single cell in sheet1 to a merged cell on another sheet
the single cell is not merged so Greg's code will not work on it.

Regular wrap text and autofit should work on single cells in sheet1Gord Dibben MS Excel MVP

On Fri, 1 Aug 2008 08:23:01 -0700, Terry H. lt;Terry
gt; wrote:

gt;Hi Greg,
gt;
gt;Great code! Works like a charm. I found an area where this does not appear
gt;to work. Any help you can provide would be greatly appreciated. I have an
gt;Excel spreadsheet where information in entered in cells on one tab. Those
gt;cells are linked to merged cells on another tab. This code does not appear
gt;to work when applied to the merged cells that are linked. Any way around
gt;this? Thanks,
gt;
gt;Terry
gt;
gt;quot;Greg Wilsonquot; wrote:
gt;
gt;gt; Paste the following to the worksheet's code module. The code assumes that
gt;gt; each cell within A1:A10 is merged to adjacent columns as opposed to these
gt;gt; cells being merged - e.g. A11 are merged, A22 are merged, A33 are
gt;gt; merged etc. Change the range reference to suit. Can be a single cell.
gt;gt;
gt;gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt;gt; Dim NewRwHt As Single
gt;gt; Dim cWdth As Single, MrgeWdth As Single
gt;gt; Dim r As Range, c As Range, cc As Range
gt;gt; Dim ma As Range
gt;gt;
gt;gt; Set r = Range(quot;A1:A10quot;)
gt;gt; If Not Intersect(Target, r) Is Nothing Then
gt;gt; Set c = Target.Cells(1, 1)
gt;gt; cWdth = c.ColumnWidth
gt;gt; Set ma = c.MergeArea
gt;gt; For Each cc In ma.Cells
gt;gt; MrgeWdth = MrgeWdth cc.ColumnWidth
gt;gt; Next
gt;gt; Application.ScreenUpdating = False
gt;gt; ma.MergeCells = False
gt;gt; c.ColumnWidth = MrgeWdth
gt;gt; c.EntireRow.AutoFit
gt;gt; NewRwHt = c.RowHeight
gt;gt; c.ColumnWidth = cWdth
gt;gt; ma.MergeCells = True
gt;gt; ma.RowHeight = NewRwHt
gt;gt; cWdth = 0: MrgeWdth = 0
gt;gt; Application.ScreenUpdating = True
gt;gt; End If
gt;gt; End Sub
gt;gt;
gt;gt; Alternatively, size the column width of a single cell in the same row to the
gt;gt; combined column widths of the merged range. Format the font, wraptext and
gt;gt; alignment exactly the same except change the font colour to be the same as
gt;gt; the background (to hide it). Enter a formula that references the active cell
gt;gt; of the merged range (e.g. quot;=C10quot;). Use the worksheet_change event to force
gt;gt; autofit of this cell. The merged cell range will then autofit along with it.
gt;gt; This assumes it is columns that are merged. Use the same logic if rows are
gt;gt; merged.
gt;gt;
gt;gt; Regards,
gt;gt; Greg
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt; quot;Stephen Sandorquot; wrote:
gt;gt;
gt;gt; gt; I have merged a number of cells and included text that wraps in the cell. Is
gt;gt; gt; it possible to set the cell so that the height is automatically adjusted to
gt;gt; gt; the height of the text?Thanks. That makes sense, and I can probably set things up so there is a
single cell to a single cell linkage. So I think my question is really more
about the linkage between cells instead of the merging cells.

Let's say I have a worksheet tab for data entry (sheet1) that has a cell
linked to a cell in another tab (sheet2) - one cell linking to one cell. The
destination cell (in sheet2) will not autofit to the wrapped text. Instead,
I have to manually resize the row to see all of the entered text. Is there a
way to set it up so that if data is entered into the cell in sheet1 the cell
height in sheet2 automatically expands to accommodate all of the text without
a manual adjustment?

quot;Gord Dibbenquot; wrote:

gt; If you are linking a single cell in sheet1 to a merged cell on another sheet
gt; the single cell is not merged so Greg's code will not work on it.
gt;
gt; Regular wrap text and autofit should work on single cells in sheet1
gt;
gt;
gt; Gord Dibben MS Excel MVP
gt;
gt; On Fri, 1 Aug 2008 08:23:01 -0700, Terry H. lt;Terry
gt; gt; wrote:
gt;
gt; gt;Hi Greg,
gt; gt;
gt; gt;Great code! Works like a charm. I found an area where this does not appear
gt; gt;to work. Any help you can provide would be greatly appreciated. I have an
gt; gt;Excel spreadsheet where information is entered in cells on one tab. Those
gt; gt;cells are linked to merged cells on another tab. This code does not appear
gt; gt;to work when applied to the merged cells that are linked. Any way around
gt; gt;this? Thanks,
gt; gt;
gt; gt;Terry
gt; gt;
gt; gt;quot;Greg Wilsonquot; wrote:
gt; gt;
gt; gt;gt; Paste the following to the worksheet's code module. The code assumes that
gt; gt;gt; each cell within A1:A10 is merged to adjacent columns as opposed to these
gt; gt;gt; cells being merged - e.g. A11 are merged, A22 are merged, A33 are
gt; gt;gt; merged etc. Change the range reference to suit. Can be a single cell.
gt; gt;gt;
gt; gt;gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; gt;gt; Dim NewRwHt As Single
gt; gt;gt; Dim cWdth As Single, MrgeWdth As Single
gt; gt;gt; Dim r As Range, c As Range, cc As Range
gt; gt;gt; Dim ma As Range
gt; gt;gt;
gt; gt;gt; Set r = Range(quot;A1:A10quot;)
gt; gt;gt; If Not Intersect(Target, r) Is Nothing Then
gt; gt;gt; Set c = Target.Cells(1, 1)
gt; gt;gt; cWdth = c.ColumnWidth
gt; gt;gt; Set ma = c.MergeArea
gt; gt;gt; For Each cc In ma.Cells
gt; gt;gt; MrgeWdth = MrgeWdth cc.ColumnWidth
gt; gt;gt; Next
gt; gt;gt; Application.ScreenUpdating = False
gt; gt;gt; ma.MergeCells = False
gt; gt;gt; c.ColumnWidth = MrgeWdth
gt; gt;gt; c.EntireRow.AutoFit
gt; gt;gt; NewRwHt = c.RowHeight
gt; gt;gt; c.ColumnWidth = cWdth
gt; gt;gt; ma.MergeCells = True
gt; gt;gt; ma.RowHeight = NewRwHt
gt; gt;gt; cWdth = 0: MrgeWdth = 0
gt; gt;gt; Application.ScreenUpdating = True
gt; gt;gt; End If
gt; gt;gt; End Sub
gt; gt;gt;
gt; gt;gt; Alternatively, size the column width of a single cell in the same row to the
gt; gt;gt; combined column widths of the merged range. Format the font, wraptext and
gt; gt;gt; alignment exactly the same except change the font colour to be the same as
gt; gt;gt; the background (to hide it). Enter a formula that references the active cell
gt; gt;gt; of the merged range (e.g. quot;=C10quot;). Use the worksheet_change event to force
gt; gt;gt; autofit of this cell. The merged cell range will then autofit along with it.
gt; gt;gt; This assumes it is columns that are merged. Use the same logic if rows are
gt; gt;gt; merged.
gt; gt;gt;
gt; gt;gt; Regards,
gt; gt;gt; Greg
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; quot;Stephen Sandorquot; wrote:
gt; gt;gt;
gt; gt;gt; gt; I have merged a number of cells and included text that wraps in the cell. Is
gt; gt;gt; gt; it possible to set the cell so that the height is automatically adjusted to
gt; gt;gt; gt; the height of the text?
gt;
gt;

If destination cell is set to wrap text and autofit you still have to
d-click on the bottom edge of the row header.

I don't know if the results of a formula will trigger the autofit.

Won't on my 2003 version.Gord

On Fri, 1 Aug 2008 14:16:03 -0700, Terry H.
gt; wrote:

gt;Thanks. That makes sense, and I can probably set things up so there is a
gt;single cell to a single cell linkage. So I think my question is really more
gt;about the linkage between cells instead of the merging cells.
gt;
gt;Let's say I have a worksheet tab for data entry (sheet1) that has a cell
gt;linked to a cell in another tab (sheet2) - one cell linking to one cell. The
gt;destination cell (in sheet2) will not autofit to the wrapped text. Instead,
gt;I have to manually resize the row to see all of the entered text. Is there a
gt;way to set it up so that if data is entered into the cell in sheet1 the cell
gt;height in sheet2 automatically expands to accommodate all of the text without
gt;a manual adjustment?
gt;
gt;quot;Gord Dibbenquot; wrote:
gt;
gt;gt; If you are linking a single cell in sheet1 to a merged cell on another sheet
gt;gt; the single cell is not merged so Greg's code will not work on it.
gt;gt;
gt;gt; Regular wrap text and autofit should work on single cells in sheet1
gt;gt;
gt;gt;
gt;gt; Gord Dibben MS Excel MVP
gt;gt;
gt;gt; On Fri, 1 Aug 2008 08:23:01 -0700, Terry H. lt;Terry
gt;gt; gt; wrote:
gt;gt;
gt;gt; gt;Hi Greg,
gt;gt; gt;
gt;gt; gt;Great code! Works like a charm. I found an area where this does not appear
gt;gt; gt;to work. Any help you can provide would be greatly appreciated. I have an
gt;gt; gt;Excel spreadsheet where information is entered in cells on one tab. Those
gt;gt; gt;cells are linked to merged cells on another tab. This code does not appear
gt;gt; gt;to work when applied to the merged cells that are linked. Any way around
gt;gt; gt;this? Thanks,
gt;gt; gt;
gt;gt; gt;Terry
gt;gt; gt;
gt;gt; gt;quot;Greg Wilsonquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; Paste the following to the worksheet's code module. The code assumes that
gt;gt; gt;gt; each cell within A1:A10 is merged to adjacent columns as opposed to these
gt;gt; gt;gt; cells being merged - e.g. A11 are merged, A22 are merged, A33 are
gt;gt; gt;gt; merged etc. Change the range reference to suit. Can be a single cell.
gt;gt; gt;gt;
gt;gt; gt;gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt;gt; gt;gt; Dim NewRwHt As Single
gt;gt; gt;gt; Dim cWdth As Single, MrgeWdth As Single
gt;gt; gt;gt; Dim r As Range, c As Range, cc As Range
gt;gt; gt;gt; Dim ma As Range
gt;gt; gt;gt;
gt;gt; gt;gt; Set r = Range(quot;A1:A10quot;)
gt;gt; gt;gt; If Not Intersect(Target, r) Is Nothing Then
gt;gt; gt;gt; Set c = Target.Cells(1, 1)
gt;gt; gt;gt; cWdth = c.ColumnWidth
gt;gt; gt;gt; Set ma = c.MergeArea
gt;gt; gt;gt; For Each cc In ma.Cells
gt;gt; gt;gt; MrgeWdth = MrgeWdth cc.ColumnWidth
gt;gt; gt;gt; Next
gt;gt; gt;gt; Application.ScreenUpdating = False
gt;gt; gt;gt; ma.MergeCells = False
gt;gt; gt;gt; c.ColumnWidth = MrgeWdth
gt;gt; gt;gt; c.EntireRow.AutoFit
gt;gt; gt;gt; NewRwHt = c.RowHeight
gt;gt; gt;gt; c.ColumnWidth = cWdth
gt;gt; gt;gt; ma.MergeCells = True
gt;gt; gt;gt; ma.RowHeight = NewRwHt
gt;gt; gt;gt; cWdth = 0: MrgeWdth = 0
gt;gt; gt;gt; Application.ScreenUpdating = True
gt;gt; gt;gt; End If
gt;gt; gt;gt; End Sub
gt;gt; gt;gt;
gt;gt; gt;gt; Alternatively, size the column width of a single cell in the same row to the
gt;gt; gt;gt; combined column widths of the merged range. Format the font, wraptext and
gt;gt; gt;gt; alignment exactly the same except change the font colour to be the same as
gt;gt; gt;gt; the background (to hide it). Enter a formula that references the active cell
gt;gt; gt;gt; of the merged range (e.g. quot;=C10quot;). Use the worksheet_change event to force
gt;gt; gt;gt; autofit of this cell. The merged cell range will then autofit along with it.
gt;gt; gt;gt; This assumes it is columns that are merged. Use the same logic if rows are
gt;gt; gt;gt; merged.
gt;gt; gt;gt;
gt;gt; gt;gt; Regards,
gt;gt; gt;gt; Greg
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; quot;Stephen Sandorquot; wrote:
gt;gt; gt;gt;
gt;gt; gt;gt; gt; I have merged a number of cells and included text that wraps in the cell. Is
gt;gt; gt;gt; gt; it possible to set the cell so that the height is automatically adjusted to
gt;gt; gt;gt; gt; the height of the text?
gt;gt;
gt;gt; Hello

Would somebody mind to please explain this auto set row height in very
beginner terms? I am not in any way Excel Savvy. But I do have one very
simple project in Excel I am trying to complete. Very simply, I have a
quot;masterquot; workbook that is to update other workbooks with exact text entered
in the master. No calculation or other fancy stuff, just the means of
entering text once and having it be copied to other workbooks. I am learning
as I go and for the most part I have a good handle on this simple process.
There are areas where an auto height adjustment of both the source field and
the targeted fields would be very useful. Cells do not need to be merged
cells. Simple copy/paste example would be greatly appreciated.
For an example that I could copy/past:
the source cell could be A19 and the target cell in a different workbook
could be B37.

Thank you for anyone who wishes to take the time to explain how this is put
into play
Nelson

quot;Gord Dibbenquot; wrote:

gt; If destination cell is set to wrap text and autofit you still have to
gt; d-click on the bottom edge of the row header.
gt;
gt; I don't know if the results of a formula will trigger the autofit.
gt;
gt; Won't on my 2003 version.
gt;
gt;
gt; Gord
gt;
gt; On Fri, 1 Aug 2008 14:16:03 -0700, Terry H.
gt; gt; wrote:
gt;
gt; gt;Thanks. That makes sense, and I can probably set things up so there is a
gt; gt;single cell to a single cell linkage. So I think my question is really more
gt; gt;about the linkage between cells instead of the merging cells.
gt; gt;
gt; gt;Let's say I have a worksheet tab for data entry (sheet1) that has a cell
gt; gt;linked to a cell in another tab (sheet2) - one cell linking to one cell. The
gt; gt;destination cell (in sheet2) will not autofit to the wrapped text. Instead,
gt; gt;I have to manually resize the row to see all of the entered text. Is there a
gt; gt;way to set it up so that if data is entered into the cell in sheet1 the cell
gt; gt;height in sheet2 automatically expands to accommodate all of the text without
gt; gt;a manual adjustment?
gt; gt;
gt; gt;quot;Gord Dibbenquot; wrote:
gt; gt;
gt; gt;gt; If you are linking a single cell in sheet1 to a merged cell on another sheet
gt; gt;gt; the single cell is not merged so Greg's code will not work on it.
gt; gt;gt;
gt; gt;gt; Regular wrap text and autofit should work on single cells in sheet1
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; Gord Dibben MS Excel MVP
gt; gt;gt;
gt; gt;gt; On Fri, 1 Aug 2008 08:23:01 -0700, Terry H. lt;Terry
gt; gt;gt; gt; wrote:
gt; gt;gt;
gt; gt;gt; gt;Hi Greg,
gt; gt;gt; gt;
gt; gt;gt; gt;Great code! Works like a charm. I found an area where this does not appear
gt; gt;gt; gt;to work. Any help you can provide would be greatly appreciated. I have an
gt; gt;gt; gt;Excel spreadsheet where information is entered in cells on one tab. Those
gt; gt;gt; gt;cells are linked to merged cells on another tab. This code does not appear
gt; gt;gt; gt;to work when applied to the merged cells that are linked. Any way around
gt; gt;gt; gt;this? Thanks,
gt; gt;gt; gt;
gt; gt;gt; gt;Terry
gt; gt;gt; gt;
gt; gt;gt; gt;quot;Greg Wilsonquot; wrote:
gt; gt;gt; gt;
gt; gt;gt; gt;gt; Paste the following to the worksheet's code module. The code assumes that
gt; gt;gt; gt;gt; each cell within A1:A10 is merged to adjacent columns as opposed to these
gt; gt;gt; gt;gt; cells being merged - e.g. A11 are merged, A22 are merged, A33 are
gt; gt;gt; gt;gt; merged etc. Change the range reference to suit. Can be a single cell.
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; gt;gt; gt;gt; Dim NewRwHt As Single
gt; gt;gt; gt;gt; Dim cWdth As Single, MrgeWdth As Single
gt; gt;gt; gt;gt; Dim r As Range, c As Range, cc As Range
gt; gt;gt; gt;gt; Dim ma As Range
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; Set r = Range(quot;A1:A10quot;)
gt; gt;gt; gt;gt; If Not Intersect(Target, r) Is Nothing Then
gt; gt;gt; gt;gt; Set c = Target.Cells(1, 1)
gt; gt;gt; gt;gt; cWdth = c.ColumnWidth
gt; gt;gt; gt;gt; Set ma = c.MergeArea
gt; gt;gt; gt;gt; For Each cc In ma.Cells
gt; gt;gt; gt;gt; MrgeWdth = MrgeWdth cc.ColumnWidth
gt; gt;gt; gt;gt; Next
gt; gt;gt; gt;gt; Application.ScreenUpdating = False
gt; gt;gt; gt;gt; ma.MergeCells = False
gt; gt;gt; gt;gt; c.ColumnWidth = MrgeWdth
gt; gt;gt; gt;gt; c.EntireRow.AutoFit
gt; gt;gt; gt;gt; NewRwHt = c.RowHeight
gt; gt;gt; gt;gt; c.ColumnWidth = cWdth
gt; gt;gt; gt;gt; ma.MergeCells = True
gt; gt;gt; gt;gt; ma.RowHeight = NewRwHt
gt; gt;gt; gt;gt; cWdth = 0: MrgeWdth = 0
gt; gt;gt; gt;gt; Application.ScreenUpdating = True
gt; gt;gt; gt;gt; End If
gt; gt;gt; gt;gt; End Sub
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; Alternatively, size the column width of a single cell in the same row to the
gt; gt;gt; gt;gt; combined column widths of the merged range. Format the font, wraptext and
gt; gt;gt; gt;gt; alignment exactly the same except change the font colour to be the same as
gt; gt;gt; gt;gt; the background (to hide it). Enter a formula that references the active cell
gt; gt;gt; gt;gt; of the merged range (e.g. quot;=C10quot;). Use the worksheet_change event to force
gt; gt;gt; gt;gt; autofit of this cell. The merged cell range will then autofit along with it.
gt; gt;gt; gt;gt; This assumes it is columns that are merged. Use the same logic if rows are
gt; gt;gt; gt;gt; merged.
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; Regards,
gt; gt;gt; gt;gt; Greg
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; quot;Stephen Sandorquot; wrote:
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; gt; I have merged a number of cells and included text that wraps in the cell. Is
gt; gt;gt; gt;gt; gt; it possible to set the cell so that the height is automatically adjusted to
gt; gt;gt; gt;gt; gt; the height of the text?
gt; gt;gt;
gt; gt;gt;
gt;
gt;

I need to do exactly this in Excel 2007. I am not able to get the row auto
height to work. I can't even double click on the row header to make it
happen. Only dragging each individual row is working. I would greatly
appreciate some assistance getting this module to work in 2007. Thanks much!
quot;Greg Wilsonquot; wrote:

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


I'm not sure whats happened there but drop this in to the worksheet code
module:

Code:
-------------------- Private Sub Worksheet_SelectionChange(ByVal Target As Range) Target.Rows.EntireRow.AutoFit End Sub
--------------------
every row you click should autofit!--
The Code Cage Team

Regards,
The Code Cage Team
'The Code Cage' (www.thecodecage.com)
------------------------------------------------------------------------
The Code Cage Team's Profile: www.thecodecage.com/forumz/member.php?userid=2
View this thread: www.thecodecage.com/forumz/sh...ad.php?t=37732I am having the same problem. Is it an Excel 2007 glitch?
Did you find a solution that works?

THX
Carol

quot;Datadonnaquot; wrote:

gt; I need to do exactly this in Excel 2007. I am not able to get the row auto
gt; height to work. I can't even double click on the row header to make it
gt; happen. Only dragging each individual row is working. I would greatly
gt; appreciate some assistance getting this module to work in 2007. Thanks much!
gt;
gt;
gt;
gt; quot;Greg Wilsonquot; wrote:
gt;
gt; gt; Paste the following to the worksheet's code module. The code assumes that
gt; gt; each cell within A1:A10 is merged to adjacent columns as opposed to these
gt; gt; cells being merged - e.g. A11 are merged, A22 are merged, A33 are
gt; gt; merged etc. Change the range reference to suit. Can be a single cell.
gt; gt;
gt; gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; gt; Dim NewRwHt As Single
gt; gt; Dim cWdth As Single, MrgeWdth As Single
gt; gt; Dim r As Range, c As Range, cc As Range
gt; gt; Dim ma As Range
gt; gt;
gt; gt; Set r = Range(quot;A1:A10quot;)
gt; gt; If Not Intersect(Target, r) Is Nothing Then
gt; gt; Set c = Target.Cells(1, 1)
gt; gt; cWdth = c.ColumnWidth
gt; gt; Set ma = c.MergeArea
gt; gt; For Each cc In ma.Cells
gt; gt; MrgeWdth = MrgeWdth cc.ColumnWidth
gt; gt; Next
gt; gt; Application.ScreenUpdating = False
gt; gt; ma.MergeCells = False
gt; gt; c.ColumnWidth = MrgeWdth
gt; gt; c.EntireRow.AutoFit
gt; gt; NewRwHt = c.RowHeight
gt; gt; c.ColumnWidth = cWdth
gt; gt; ma.MergeCells = True
gt; gt; ma.RowHeight = NewRwHt
gt; gt; cWdth = 0: MrgeWdth = 0
gt; gt; Application.ScreenUpdating = True
gt; gt; End If
gt; gt; End Sub
gt; gt;
gt; gt; Alternatively, size the column width of a single cell in the same row to the
gt; gt; combined column widths of the merged range. Format the font, wraptext and
gt; gt; alignment exactly the same except change the font colour to be the same as
gt; gt; the background (to hide it). Enter a formula that references the active cell
gt; gt; of the merged range (e.g. quot;=C10quot;). Use the worksheet_change event to force
gt; gt; autofit of this cell. The merged cell range will then autofit along with it.
gt; gt; This assumes it is columns that are merged. Use the same logic if rows are
gt; gt; merged.
gt; gt;
gt; gt; Regards,
gt; gt; Greg
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;Stephen Sandorquot; wrote:
gt; gt;
gt; gt; gt; I have merged a number of cells and included text that wraps in the cell. Is
gt; gt; gt; it possible to set the cell so that the height is automatically adjusted to
gt; gt; gt; the height of the text?