Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
autofit text inside merged cells
How do I autofit text inside of a merged cell (3 cells that have been merged
into one)? I would like the row to expand as needed, depending on the amount of text I input. But I'm learning that autofit only works inside of a single, un-merged cell. Is there anyway around this? Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
autofit text inside merged cells
The only way around it is to...............
1. DON'T USE merged cells. They cause no end of problems with copying, pasting, sorting, filtering and, as you have found, with autofit. 2. Use event code from Greg Wilson Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range With Target If .MergeCells And .WrapText 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 With End Sub This is heet event code. Right-click on the sheet tab and "View Code". Copy/paste the code into that sheet module. Gord Dibben MS Excel MVP On Tue, 31 Jul 2007 13:04:05 -0700, Taurus_ma wrote: How do I autofit text inside of a merged cell (3 cells that have been merged into one)? I would like the row to expand as needed, depending on the amount of text I input. But I'm learning that autofit only works inside of a single, un-merged cell. Is there anyway around this? Thanks! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
autofit text inside merged cells
I followed this instruction and it worked well until I added protection to
the sheet. The cell I needed to expand was not locked but the box no longer grew. Is there additional part(s) to the code that would allow this to work? "Gord Dibben" wrote: The only way around it is to............... 1. DON'T USE merged cells. They cause no end of problems with copying, pasting, sorting, filtering and, as you have found, with autofit. 2. Use event code from Greg Wilson Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range With Target If .MergeCells And .WrapText 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 With End Sub This is heet event code. Right-click on the sheet tab and "View Code". Copy/paste the code into that sheet module. Gord Dibben MS Excel MVP On Tue, 31 Jul 2007 13:04:05 -0700, Taurus_ma wrote: How do I autofit text inside of a merged cell (3 cells that have been merged into one)? I would like the row to expand as needed, depending on the amount of text I input. But I'm learning that autofit only works inside of a single, un-merged cell. Is there anyway around this? Thanks! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
autofit text inside merged cells
Gord - can you please explain to me in laymens terms what you are supposed to
copy and paste? That whole blurb from "Private Sub..." to "... End Sub"? And am I to paste it in the actual merged cell I wish to Autofit? THANKS!!!! "Gord Dibben" wrote: The only way around it is to............... 1. DON'T USE merged cells. They cause no end of problems with copying, pasting, sorting, filtering and, as you have found, with autofit. 2. Use event code from Greg Wilson Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range With Target If .MergeCells And .WrapText 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 With End Sub This is heet event code. Right-click on the sheet tab and "View Code". Copy/paste the code into that sheet module. Gord Dibben MS Excel MVP On Tue, 31 Jul 2007 13:04:05 -0700, Taurus_ma wrote: How do I autofit text inside of a merged cell (3 cells that have been merged into one)? I would like the row to expand as needed, depending on the amount of text I input. But I'm learning that autofit only works inside of a single, un-merged cell. Is there anyway around this? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there a way to Autofit Merged Cells in a row? | Excel Discussion (Misc queries) | |||
Autofit with Merged Cells/Wrap Text Macro Problem | Excel Discussion (Misc queries) | |||
Row Autofit on Merged Cells | Excel Discussion (Misc queries) | |||
Autofit in Merged Cells? | Excel Discussion (Misc queries) | |||
How do I "Wrap Text" & "Autofit" within Merged Cells in Excel? | Excel Worksheet Functions |