PDA

View Full Version : Autofit merged cells



070968
03-26-2006, 09:57 AM
Hi,

I need to adjust the row height automatically,when I enter the text in the merged cell. Row height should increase or decrease, as the length of the text increases or decreases. But I do not want to disturb the column width.

I am new to VBA , please help me.

Thanks

070968

mdmackillop
03-26-2006, 11:00 AM
Here's some code by Jim Rech (Excel MVP) adjusted to accept triggering from the worksheet event.
Regards
MD


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.MergeCells Then AutoFitMergedCellRowHeight Target
Application.EnableEvents = True
Target.Offset(1).Activate
End Sub

''Simulates row height autofit for a merged cell if the active cell..
'' is merged.
'' has Wrap Text set.
'' includes only 1 row.
''Unlike real autosizing the macro only increases row height
'' (if needed). It does not reduce row height (because another
'' merged cell on the same row may needed a greater height
'' than the active cell).
Sub AutoFitMergedCellRowHeight(Target As Range)
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single
Target.Activate
'Remove comment to allow row to reduce
'Target.Rows.AutoFit
If ActiveCell.MergeCells Then
With ActiveCell.MergeArea
If .Rows.Count = 1 And .WrapText = True Then
Application.ScreenUpdating = False
CurrentRowHeight = .RowHeight
ActiveCellWidth = ActiveCell.ColumnWidth
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth + MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
End Sub

070968
03-26-2006, 11:18 AM
I put the given code in the this workbook pane but it is not working! Do I need to put any other code with it?

mdmackillop
03-26-2006, 11:25 AM
See attached sample

070968
03-26-2006, 11:41 AM
it's working in your file. but not in mine. Is there any installation problem?

mdmackillop
03-26-2006, 11:49 AM
There are two bits to the code The first bit

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.MergeCells Then AutoFitMergedCellRowHeight Target
Application.EnableEvents = True
Target.Offset(1).Activate
End Sub


goes into a Worksheet module. This monitors changes and triggers a macro when a change occurs. The code needs to be pasted in each worksheet contasining your merged cells.
The remainder of the code goes in a standard module so it can be used from any worksheet.

070968
03-26-2006, 12:11 PM
sorry but when I am compiling the code, it is showing following error :

"AutoFitMergedCellRowHeight" sub or function not defined:blush

mdmackillop
03-26-2006, 12:14 PM
Can you post your workbook. Use Manage attachments in the Go Advanced section. Remove any sensitive data first

070968
03-26-2006, 12:34 PM
please see the attachment.

mdmackillop
03-26-2006, 12:42 PM
The main code has to go into a Standard module. Use Insert Module to create one.
I note that your merged cell is two rows high. This code will only work with single row merged cells (see comments in code)

070968
03-26-2006, 12:57 PM
sorry, I checked the file sent by you again, I merged cells in a row, but still it is not working in that file also.:blush

070968
03-27-2006, 08:42 AM
Thanks Autofit file sent by u is working now, I re installed my Office Xp then it started working.

Please do me one more favour - I need Justified Indent in excel file.


Thanks a lot once again.

mdmackillop
03-27-2006, 08:56 AM
As this is not associated with this topic, you'd be better to ask this as a new question
Regards
MD

070968
03-27-2006, 09:02 AM
I am attaching my file, I use one file to feed data and others gets filled. Now the autofit works only when i enter the data in cell. I want to happen this when i switch to file.

Please help.

mdmackillop
03-27-2006, 09:37 AM
This use is really not appropriate to Excel. You would be much better off creating a mail merge document in Word, using Excel as the record source. Excel cannot properly format text and cell sizes etc. do not typically appear in print as they appear on the screen once you have multiple text lines. This would remove the need for complex macros, which at best can only do a partial job with this sort of document.
Regards
MD