Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Row Height for merged cells
I want to set the row height for a row containing merged cells with long text that wraps.
Autoheight doesn't work. The only way I can think to do it is to demerge the cells, select the range, format for centre across selection, autofit. At this point I would note the row height and then merge the cells and set the height to that noted. I need the cells merged to use justified alignment. If you can think of a better way I would appreciate. Kaval |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Row Height for merged cells
You're quite insightful. The approach you describe was
developed by Jim Rech some time back and as far as I know is the method of choice. I wrote a variation of Jim's code as shown below. If you decide to use it, change the cell references to suit. Note that the Wraptext property of the merged ranges must be set to True. The code assumes that the merged cells are within the range A1:C10 with only the columns merged (i.e. A1:C1, A2:C2, A3:C3 etc. merged). Paste to the worksheet code module. Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Dim RowHt As Single, MergeWidth As Single Dim C As Range, AutoFitRng As Range Dim CWidth As Single, NewRowHt As Single Static OldRng As Range On Error Resume Next If Target.Count 3 Then Exit Sub 'Change to suit If OldRng Is Nothing Then _ Set OldRng = Range("A1").MergeArea Set AutoFitRng = Range("A1:C10") If Not Intersect(OldRng, AutoFitRng) Is Nothing Then Application.ScreenUpdating = False With OldRng RowHt = .RowHeight CWidth = .Cells(1).ColumnWidth For Each C In OldRng MergeWidth = C.ColumnWidth + MergeWidth Next .MergeCells = False .Cells(1).ColumnWidth = MergeWidth .EntireRow.AutoFit NewRowHt = .RowHeight .Cells(1).ColumnWidth = CWidth .MergeCells = True .RowHeight = NewRowHt End With Application.ScreenUpdating = True End If Set OldRng = Target End Sub Regards, Greg -----Original Message----- I want to set the row height for a row containing merged cells with long text that wraps. Autoheight doesn't work. The only way I can think to do it is to demerge the cells, select the range, format for centre across selection, autofit. At this point I would note the row height and then merge the cells and set the height to that noted. I need the cells merged to use justified alignment. If you can think of a better way I would appreciate. Kaval . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merged cells won't Autofit row height | Excel Discussion (Misc queries) | |||
Merged cells won't autofit row height on other computers | Excel Discussion (Misc queries) | |||
Row height using autofit, with no merged cells | Excel Discussion (Misc queries) | |||
Autofit Row Height of Merged Cells | Excel Discussion (Misc queries) | |||
Autofit row height in merged cells | Excel Discussion (Misc queries) |