Automatically changing the rowheight
 

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

Automatically changing the rowheight
 

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

"Ajit" wrote in message
...
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.

Thanks

Automatically changing the rowheight
 

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.