setting the auto row height using vba

[Lucky]

I have an Excel spreadsheet that is created by an Access
database. There is a merged cell area that is a
description that can be any number of characters. Is
there code in vb that can do the autosize for a merged
cell? Please help.

Lucky

 

[Greg Wilson]

The following is an adaption of Jim Rech's code on the
subject that I wrote some time ago. Note that the
WrapText property of the cells must be set to True and it
only works for merged columns as currently designed. The
merged range is assumed to be A1:C10. Change the code to
suit. Paste to the worksheet code module. Not tested
rigorously and never actually used by me for any project.

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 OldRng Is Nothing Then _
Set OldRng = Range("A1").MergeArea 'Change to suit.
Set AutoFitRng = Range("A1:C10")'Change to suit.
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

 

[Greg Wilson]

When I said "merged range is assumed to be A1:C10" I meant
that A1:C1 are merged, A2:C2 are merged etc. up to A10:C10.

Regards,
Greg

 

[Lucky]

Thanks! I will try that when I get back to work on Monday.
Have a wonderful Easter.