Automatically expand merged cells multiple sheets

[AZExcel]

Hello Friends,


I am looking for a solution that automatically expands merged cells for multiple sheets; 1,2


I am working on a project that requires user input on sheet 1 in merged cells;C14:G14. My 1st challenge is to have the merged range expand automatically as the text expands.


Part 2 of the challenge is a have sheet 2 that is a mirror of sheet for the most part

(I am using ='Sheet 1'!C14:G14 to accomplish this) do the same thing...

I have been working with the code below which does what I need in the active cell.

Any help would be appreciated !!

[pre]

Sub AutoFitMergedCellRowHeight()
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single
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
Application.ScreenUpdating = True
End Sub

[/pre]

 

[Luke M]

First, I would make a plea that you abandon the use of merged cells, especially if you're going to be using VBA. Merged cells cause countless problems with writing code, and require lengthy amounts of error handling usually to accomplish what would normally be simple operations. Usually, you can get by w/o using Merged cells by just changing the formatting of the cells.


For instance, it appears your code is designed to increase the row height for a merged cell to fit the needed height. A workaround would be to get rid of the merged cell, and instead used the Horizontal Alignment of "Center Across Selection". You can then AutoFit the row height, and you'll get the same appearance. Now that the merged cell is gone, copying the value/format to another sheet is much simpler.


If you really must have the merged cells, I suggest reading here:

http://blog.contextures.com/archives/2012/06/07/autofit-merged-cell-row-height/

where Debra uses a similar technique to get the correct heights for merged cells.

 

[AZExcel]

Thanks Luke,


My research of this topic finds the same recommendation against merged cells, but I did not know why, thanks for your added insight.


I will work with changing the formatting as you suggest as this seems to be the most efficient way, and because I am very new to VBA and want to use the best practices.


How would I change the code above to work with the new formatting?

 

[Luke M]

If there's no longer any merged cells in the range, you should be able to do a one-liner like this:

Range("14:30").EntireRow.AutoFit

 

[AZExcel]

Luke,


This may be a stupid question, but where would the one-liner go? As I mentioned I am very new to VBA. I have worked with a few macros which I assigned in modules. I then created buttons to execute the code.


What your suggesting seems different than that. Once the one-liner is placed, will it automatically work?