Autofit merged cells

[070968]

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]

Here's some code by Jim Rech (Excel MVP) adjusted to accept triggering from the worksheet event.
Regards
MD

[VBA]
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

[/VBA]

[070968]

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]

See attached sample

[070968]

it's working in your file. but not in mine. Is there any installation problem?

[mdmackillop]

There are two bits to the code The first bit
[vba]
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

[/vba]
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]

sorry but when I am compiling the code, it is showing following error :

"AutoFitMergedCellRowHeight" sub or function not defined

[mdmackillop]

Can you post your workbook. Use Manage attachments in the Go Advanced section. Remove any sensitive data first

[070968]

please see the attachment.

[mdmackillop]

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]

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.

[070968]

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]

As this is not associated with this topic, you'd be better to ask this as a new question
Regards
MD

[070968]

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]

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