Automatically changing the rowheight

[Ajit]

I have a cell(merged)-(lets say cell-1) which is being fed from the input from the form. In the excel sheet i have around 20 cells (some of them merged) which references to the cell-1. To all the 20 cells i have =cell-1 formula written.

Is there a way that if i increase or decrease the rowheight of cell-1, all the rowheights change accordingly

Note : I tried autofit but that doesn't work fine with merged cells.

Thank

 

[Tom Ogilvy]

Perhaps you can adapt this code posted previously by Jim Rech, to achieve
what you want:

Jim Rech

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
End Sub


--
Regards,
Tom Ogilvy

I have a cell(merged)-(lets say cell-1) which is being fed from the input

from the form. In the excel sheet i have around 20 cells (some of them
merged) which references to the cell-1. To all the 20 cells i have =cell-1
formula written.

Is there a way that if i increase or decrease the rowheight of cell-1, all

the rowheights change accordingly.

 

[Ajit]

Tom
First of all Thanks for the quick reply. That code works perfect for adjusting the row height of merged cells. Thanks for that too.
Now i have two more doubt

Firstly, again back to my initial question : This code would be perfect otherwise but what i was looking that would there be a way to resize the dependencies in Excel formulae..upon change of parent cell

Second question regarding the code : There seems to be a little (very marginal error) while using this code. Lets say we have a single column with width 98.58 (to be very precise...., i did some testing with this) and i typed in something to the end of the line. and now i have five columns (all merged) totalling to column width of 98.58 (38.86 + 7.43 + 22.43 + 7.43 + 22.43) and i typed in the same text. I used the below code .....and it takes an extra line, in merged cells(looking into the code i could understand that if the cells are merged and wraptext is true it unmerges the row , increases the width of te first merged cell to the total width of all merged cells then auto fits it , calculates the row height and again merges it and sets the row height) . I know i m going little crazy as this is gonna be the rarest case...but was just curious...if you have any idea of the extra space it takes while in a single column than in a merged cell of the same column width.