-
Autofit merged cells
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
-
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]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
It is not working
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?
-
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
sorry not working
it's working in your file. but not in mine. Is there any installation problem?
-
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.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
sorry but when I am compiling the code, it is showing following error :
"AutoFitMergedCellRowHeight" sub or function not defined
-
Can you post your workbook. Use Manage attachments in the Go Advanced section. Remove any sensitive data first
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
please see the attachment.
-
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)
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
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.
-
Thanks its working now
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.
-
As this is not associated with this topic, you'd be better to ask this as a new question
Regards
MD
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
Fine, Thanks I have One more Query Regarding Auto Fit
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.
-
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
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules