setting the auto row height using vba
 

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

setting the auto row height using vba
 

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

-----Original Message-----
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
.

setting the auto row height using vba
 

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

-----Original Message-----
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

-----Original Message-----
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
.

.

setting the auto row height using vba
 

Thanks! I will try that when I get back to work on Monday.
Have a wonderful Easter.
-----Original Message-----
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

-----Original Message-----
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

-----Original Message-----
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
.

.

.