Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
where text wraps in a cell, how can the row height be auto set?
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
where text wraps in a cell, how can the row height be auto set?
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
where text wraps in a cell, how can the row height be auto set
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
where text wraps in a cell, how can the row height be auto set
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 sheet1 Gord Dibben MS Excel MVP On Fri, 1 Aug 2008 08:23:01 -0700, Terry H. <Terry wrote: Hi Greg, Great code! Works like a charm. I found an area where this does not appear to work. Any help you can provide would be greatly appreciated. I have an Excel spreadsheet where information in entered in cells on one tab. Those cells are linked to merged cells on another tab. This code does not appear to work when applied to the merged cells that are linked. Any way around this? Thanks, Terry "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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
where text wraps in a cell, how can the row height be auto set
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? "Gord Dibben" wrote: 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 sheet1 Gord Dibben MS Excel MVP On Fri, 1 Aug 2008 08:23:01 -0700, Terry H. <Terry wrote: Hi Greg, Great code! Works like a charm. I found an area where this does not appear to work. Any help you can provide would be greatly appreciated. I have an Excel spreadsheet where information is entered in cells on one tab. Those cells are linked to merged cells on another tab. This code does not appear to work when applied to the merged cells that are linked. Any way around this? Thanks, Terry "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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
where text wraps in a cell, how can the row height be auto set
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. wrote: 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? "Gord Dibben" wrote: 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 sheet1 Gord Dibben MS Excel MVP On Fri, 1 Aug 2008 08:23:01 -0700, Terry H. <Terry wrote: Hi Greg, Great code! Works like a charm. I found an area where this does not appear to work. Any help you can provide would be greatly appreciated. I have an Excel spreadsheet where information is entered in cells on one tab. Those cells are linked to merged cells on another tab. This code does not appear to work when applied to the merged cells that are linked. Any way around this? Thanks, Terry "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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
where text wraps in a cell, how can the row height be auto set
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 "master" 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 "Gord Dibben" wrote: 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. wrote: 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? "Gord Dibben" wrote: 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 sheet1 Gord Dibben MS Excel MVP On Fri, 1 Aug 2008 08:23:01 -0700, Terry H. <Terry wrote: Hi Greg, Great code! Works like a charm. I found an area where this does not appear to work. Any help you can provide would be greatly appreciated. I have an Excel spreadsheet where information is entered in cells on one tab. Those cells are linked to merged cells on another tab. This code does not appear to work when applied to the merged cells that are linked. Any way around this? Thanks, Terry "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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
where text wraps in a cell, how can the row height be auto set
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! "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? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
where text wraps in a cell, how can the row height be auto set
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' (http://www.thecodecage.com) ------------------------------------------------------------------------ The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=37732 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
where text wraps in a cell, how can the row height be auto set
I am having the same problem. Is it an Excel 2007 glitch?
Did you find a solution that works? THX Carol "Datadonna" wrote: 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! "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? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
where text wraps in a cell, how can the row height be auto set
I am having this problem, also.
Since I don't know what the "worksheet code module" is, the above does not help me. Need more explaination. "Datadonna" wrote: 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! "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? |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
where text wraps in a cell, how can the row height be auto set
If you're new to macros:
Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) Deb wrote: I am having this problem, also. Since I don't know what the "worksheet code module" is, the above does not help me. Need more explaination. "Datadonna" wrote: 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! "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? -- Dave Peterson |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
where text wraps in a cell, how can the row height be auto set
I got this code to work, thank t=you very much, I have a question however
which is: I have a number of merged cell instances within the same worksheet and workbook where I need this functionality. I cannot get this code to work for additional instances. Can you help? Thank you. "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? |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
where text wraps in a cell, how can the row height be auto set
"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? |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
where text wraps in a cell, how can the row height be auto set
I have a row with merged cells and I am trying to get the height to automatically adjust. How do I get this to happen? Linda B. "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? |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
where text wraps in a cell, how can the row height be auto set
Make appropriate changes to the range in Greg's code.
Wrap Text rowautofit must be enabled to start with. If stuck, please post details of your merged cells area(s) Gord Dibben MS Excel MVP On Mon, 27 Jul 2009 12:01:02 -0700, Linda B wrote: I have a row with merged cells and I am trying to get the height to automatically adjust. How do I get this to happen? Linda B. "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? |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
where text wraps in a cell, how can the row height be auto set
HELP! Just need to have rows auto fit contents (expand). Do not understand
the complicated code referenced in this post or even where to copy and insert this code. Why doesn't the "Auto fit Row Height" option work under formatting? "Gord Dibben" wrote: Make appropriate changes to the range in Greg's code. Wrap Text rowautofit must be enabled to start with. If stuck, please post details of your merged cells area(s) Gord Dibben MS Excel MVP On Mon, 27 Jul 2009 12:01:02 -0700, Linda B wrote: I have a row with merged cells and I am trying to get the height to automatically adjust. How do I get this to happen? Linda B. "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? |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
where text wraps in a cell, how can the row height be auto set
More info: I'm using Excel 2007 - several columns are merged and merged and
text wrap boxes are checked. "Redwren" wrote: HELP! Just need to have rows auto fit contents (expand). Do not understand the complicated code referenced in this post or even where to copy and insert this code. Why doesn't the "Auto fit Row Height" option work under formatting? "Gord Dibben" wrote: Make appropriate changes to the range in Greg's code. Wrap Text rowautofit must be enabled to start with. If stuck, please post details of your merged cells area(s) Gord Dibben MS Excel MVP On Mon, 27 Jul 2009 12:01:02 -0700, Linda B wrote: I have a row with merged cells and I am trying to get the height to automatically adjust. How do I get this to happen? Linda B. "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? |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
where text wraps in a cell, how can the row height be auto set
Plain and simple...................If you have merged cells in the range,
Autofit won't work even with wraptext enabled. You will need the code if you insist upon using merged cells. The developers added the merge cells feature in Excel 97 without thinking about row autofit functionality. Have not bothered to correct since. Gord On Tue, 29 Sep 2009 11:19:01 -0700, Redwren wrote: HELP! Just need to have rows auto fit contents (expand). Do not understand the complicated code referenced in this post or even where to copy and insert this code. Why doesn't the "Auto fit Row Height" option work under formatting? "Gord Dibben" wrote: Make appropriate changes to the range in Greg's code. Wrap Text rowautofit must be enabled to start with. If stuck, please post details of your merged cells area(s) Gord Dibben MS Excel MVP On Mon, 27 Jul 2009 12:01:02 -0700, Linda B wrote: I have a row with merged cells and I am trying to get the height to automatically adjust. How do I get this to happen? Linda B. "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? |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
where text wraps in a cell, how can the row height be auto set
"Gord Dibben" wrote: Plain and simple...................If you have merged cells in the range, Autofit won't work even with wraptext enabled. You will need the code if you insist upon using merged cells. The developers added the merge cells feature in Excel 97 without thinking about row autofit functionality. Have not bothered to correct since. Gord On Tue, 29 Sep 2009 11:19:01 -0700, Redwren wrote: HELP! Just need to have rows auto fit contents (expand). Do not understand the complicated code referenced in this post or even where to copy and insert this code. Why doesn't the "Auto fit Row Height" option work under formatting? "Gord Dibben" wrote: Make appropriate changes to the range in Greg's code. Wrap Text rowautofit must be enabled to start with. If stuck, please post details of your merged cells area(s) Gord Dibben MS Excel MVP On Mon, 27 Jul 2009 12:01:02 -0700, Linda B wrote: I have a row with merged cells and I am trying to get the height to automatically adjust. How do I get this to happen? Linda B. "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? I have carefully read through this discussion twice and I am no better off than when I started. There are obviously some very clever people trying their best to resolve a very simple problem. But clearly without success, as the same user problem is repeated many times. So to the basics. Admit it, auto fit row height for a merged and wrapped cell (eg cells B5 to J5) containing an unknown (and variable) number of charaters does not work, has never worked. Also, microsoft has known about this since 1997 or earlier. As this is a pretty much basic function, microsoft must explain why it has not been fixed. It is all very well for the clever people to provide macro answers, but most users do not know and do not use macros. So there has to be a better way that the ordinary user can easily implement. Notwithstanding the above, I am a low level macro user and the code provided above baffles me. (MS Excel 2007) I challenge microsoft to respond. |
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
where text wraps in a cell, how can the row height be auto set?
"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? |
#22
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
where text wraps in a cell, how can the row height be auto set?
I had the same issue. I found a work around for my situation. I made the
column as wider instead of merging cells, then when you select wrap text the automatic row height works. It may not work for all but does for me. "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? |
#23
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
where text wraps in a cell, how can the row height be auto set?
Ruth
Do you have a question about this previous post you tacked onto? Short answer...............merged cells do not allow row autofit unless you employ VBA event code. Gord Dibben MS Excel MVP On Fri, 22 Jan 2010 15:07:01 -0800, Ruth <Ruth @discussions.microsoft.com wrote: "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? |
#24
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
where text wraps in a cell, how can the row height be auto set
It seems pretty silly that this is not intuitive. One way I know is highlight
all the rows, then on the left hand side of the spreadsheet, where the rows are numbered double click with the left mouse button on the line between two of the rows, and bingo :) "Elso" wrote: 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 "master" 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 "Gord Dibben" wrote: 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. wrote: 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? "Gord Dibben" wrote: 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 sheet1 Gord Dibben MS Excel MVP On Fri, 1 Aug 2008 08:23:01 -0700, Terry H. <Terry wrote: Hi Greg, Great code! Works like a charm. I found an area where this does not appear to work. Any help you can provide would be greatly appreciated. I have an Excel spreadsheet where information is entered in cells on one tab. Those cells are linked to merged cells on another tab. This code does not appear to work when applied to the merged cells that are linked. Any way around this? Thanks, Terry "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? |
#25
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
where text wraps in a cell, how can the row height be auto set
Thanks Ruth. I tried this and it worked for me. I unmerged the cells and
then double clicked for auto row height and it worked. "Ruth" wrote: I had the same issue. I found a work around for my situation. I made the column as wider instead of merging cells, then when you select wrap text the automatic row height works. It may not work for all but does for me. "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? |
#26
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
where text wraps in a cell, how can the row height be auto set
I need help for a very similar problem:
NO merged cells NO specialized formatting, and format is consistent across spreadsheet cells are set to "wrap text" When I click on "autofit row height", 90% of the rows become the correct height, while 10% do not. I have a few thousand rows of data, and 26 columns with varying amounts of text in them. Sometimes the row becomes too short, sometimes too tall, and it can happen no matter how much or how little text is in it. Can I repeat that I DO NOT have any merged cells? I have searched online for hours and no one seems to have an explanation or a solution. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell Displays ### and Won't AutoFit for Height | Excel Discussion (Misc queries) | |||
Shade cell according to text? | Excel Discussion (Misc queries) | |||
How do I set text to top of cell next to wrap text in Excel? | New Users to Excel | |||
Excel - merged cells w/wrapped text auto row height doesn't work. | Excel Discussion (Misc queries) | |||
Formulas dealing with text data | Excel Worksheet Functions |