How do I make a cell expand when needed???

[AuditorDorCo]

I am producing a "Pay for Performance" worksheet to use in evaluatin
employees for a pay raise. The cell used to enter a justificatio
statement needs to expand depending on the amount of information type
into it. I have it set to "wrap" the text and it does that, but I nee
for the height of the cell to adjust if the individual needs to typ
more information. Anyone know how?????:confused

 

[Gord Dibben]

Along with the wrap-text set to "on", you need to have the row set for
"Autofit".

If you are using merged cells, the autofit won't work. You have to change the
height manually or use a Jim Rech macro.

If interested in the macro, see these threads from google search.

http://groups.google.com/groups?threadm=e1$1uzL1BHA.1784@tkmsftngp05

Note also a recent adaptation of this code by Greg Wilson.

http://makeashorterlink.com/?P37D24B15

Gord Dibben Excel MVP

 

[Guest]

I have tried the scripts referenced her, and a number of mods to them and am having no luck. Maybe one of you guru's can see something I missed.
My problem is that I have a document with 2 merged cell columns in a number of rows. They are product test and expected results descriptions, so it is of some importance for the testers to be able to read them.
When I first tried the scripts, they both failed for one reason or another. I modified one to do a single row, and it worked fine. I then put it in a loop to increment the row and I get an error, apparently randomly but always at the .Cells(1).ColumnWidth = MergedCellRgWidth line. I get a Run-time error '1004': Unable to set the ColumnWidth property of the Range class. The same code has run against some rows ok, and does not fail on the same row repetitively. I have even pasted the contents of one row that always works into the other cells, and no joy. I have also reversed to order, as you will see, and no help. Hope one of you can see something. I have 300 of these sheets with ever changing content. Thanks much... Script follows:
Sub TestMergedRowHeight()

' TestMergedRowHeight Macro
' Macro recorded 6/14/2004 by Gary Rickert
'
' Keyboard Shortcut: Ctrl+t
'
For i = 35 To 18 Step -1
Dim varRow As Single
Dim varCol As String
Dim varCell As String
varCol = "B"
varRow = ()
varCell = varCol & varRow
Range([varCell]).Select
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

varCol = "D"
varCell = varCol & varRow
Range([varCell]).Select
Range([varCell]).Select
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
MsgBox MergedCellRgWidth
.Cells(1).ColumnWidth = MergedCellRgWidth
MsgBox .Cells(1).ColumnWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf([CurrentRowHeight] > [PossNewRowHeight], CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
Next i
End Sub