Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Row height setting problem | Excel Discussion (Misc queries) | |||
setting row height not to exceed... | Excel Discussion (Misc queries) | |||
Standard Row Height setting confused | Excel Discussion (Misc queries) | |||
Setting Default Row Height | Setting up and Configuration of Excel | |||
Setting width and height | Excel Programming |