Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Resizing cells" code doesn't work with merged cells
I got a code I found on this site to resize my cells as I type:
Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Application.EnableEvents = False Rows(t.Row).EntireRow.AutoFit Application.EnableEvents = True End Sub But it doesn't work on merged cells. Is there an additional code that can be added to the above to include merged cells? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Resizing cells" code doesn't work with merged cells
You need a little more code when working with those sheet-wrecking merged cells.
Here is 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 Gord Dibben MS Excel MVP On Thu, 26 Jun 2008 08:23:01 -0700, Gina wrote: I got a code I found on this site to resize my cells as I type: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Application.EnableEvents = False Rows(t.Row).EntireRow.AutoFit Application.EnableEvents = True End Sub But it doesn't work on merged cells. Is there an additional code that can be added to the above to include merged cells? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Resizing cells" code doesn't work with merged cells
That did it!!! Thank you so much!
"Gord Dibben" wrote: You need a little more code when working with those sheet-wrecking merged cells. Here is 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 Gord Dibben MS Excel MVP On Thu, 26 Jun 2008 08:23:01 -0700, Gina wrote: I got a code I found on this site to resize my cells as I type: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Application.EnableEvents = False Rows(t.Row).EntireRow.AutoFit Application.EnableEvents = True End Sub But it doesn't work on merged cells. Is there an additional code that can be added to the above to include merged cells? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Repeat code to "n" cells | New Users to Excel | |||
Work in 2 Cells "C3" and "R3" | Excel Worksheet Functions | |||
Odd error - "This operation requires merged cells..." | New Users to Excel | |||
sort spreadsheet, "merged cells" comes up. Find cells? Unmerge ? | Excel Discussion (Misc queries) | |||
Can't copy & paste - "merged cells must be identically sized" | Excel Discussion (Misc queries) |