Auto Row Height Merged Cells (Greg Wilson's Code) Questions

**FallingDown** · 02-19-2013, 06:35 AM

Hi all

To autosize the row height of merged cells I am using Greg Wilson's Code

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

BUT, it does not seem to work if I merge a cell over more than four rows:
It ends in Runtime-Error '1004':
Unable to set columnWidth property of the range class

Excel has a problem with this line

c.ColumnWidth = MrgeWdth

Is any one else experiencing this problem?

Thanks
FD

**FallingDown** · 02-20-2013, 05:20 AM

Hi everyone,

Any help on the above would be appeciated greatly - many thanks

**FallingDown** · 02-21-2013, 09:18 AM

Hi all

No solution to this problem?
I can't understand why the code would not work when the merged cells are distributed over more than four rows

**FallingDown** · 02-25-2013, 05:20 AM

Good morning

OK, last try - no one who would know how to fix this at all?

**OllieB** · 02-25-2013, 05:39 AM

The code looks fine, and when I test in a dummy workbook (even when merging 4 rows) I do not receive any error message. I guess the column widths, row heights and size of text in the merged cell may be causing a problem. Would it be possible to post a copy of the workbook? If not, debug the code and tell us what the value of the MrgeWdth variable is when the error occurs.

**OllieB** · 02-25-2013, 05:49 AM

Had another look, the maximum width you can assign via .ColumnWidth is the value 255 (representing characters for some reason - according to the helptext). So if the total of the width of the merged columns exceeds 255, the code is going to result in an error.