make row height automatic

S

spencer

I want to reference data in another worksheet and have the row heigh
automatically adjust to fit the data referenced.
For example: in cell A1 of Sheet1 I enter =Sheet2!B3
cell b3 in sheet2 contains a paragraph of 100 characters and column
of Sheet1 is 20 characters wide
I want cell A1 in Sheet1 to automatically adjust height to accomodat
the data referenced. I have tried setting A1 to wrap and every othe
thing I can think of. Nothing works - so far.
Help
Thank
 
D

Dave H

spencer said:
I want to reference data in another worksheet and have the row height
automatically adjust to fit the data referenced.
For example: in cell A1 of Sheet1 I enter =Sheet2!B3
cell b3 in sheet2 contains a paragraph of 100 characters and column A
of Sheet1 is 20 characters wide
I want cell A1 in Sheet1 to automatically adjust height to accomodate
the data referenced. I have tried setting A1 to wrap and every other
thing I can think of. Nothing works - so far.
Help
Thanks
Click to expand...

This may not be the best way but this is how I accomplish this on my
spreadsheets. I use a formula to calculate the row height.
=roundup(len(a1)/20,0)*12.75 where 12.75 would be your normal row
height. Then I use a macro to adjust the row height to the amount
calculated. You should format the cell to wrap and as the length
increases it adds height appropriately.
 
D

Dave Peterson

Formulas won't change rowheight. You'll have to rely on something else.

Since the results change because of a formula, you could use a
worksheet_calculate event.

Rightclick on the worksheet tab with the formula. Select View code and paste
this into the code window.

Option Explicit
Private Sub Worksheet_Calculate()
'specify particular row(s)
Me.Range("a6,a12,A13").entirerow.AutoFit
'or get all the rows
'Me.UsedRange.Rows.AutoFit
End Sub

You can read more about events at:
Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
D

Dave H

Dave,

Your right formulas won't change row height but the following macro
does. I calculate the row height I want because I don't want some rows
to show. Is there a better way to do this for say just a range of cells
rather than the whole sheet?

' Assign the Range to the FormatRange variable
Set FormatRange = ActiveSheet.Range("Collapse")

' Loop thru the Adjust_Rows range to format the cell to its
' rowheight based on the value in the cell
For Each oCell In FormatRange

' Obtain the cell value which is the Row Height value
Cell_Width = oCell.Value

' Set the Row Height to the cell value obtained above
oCell.RowHeight = Cell_Width

Next oCell

' Turn Screen Updating ON
Application.ScreenUpdating = True
 
D

Dave Peterson

It looks like you could limit the range by just limiting what's in that Collapse
range.

Although, I'm confused about heights and widths <g>.

And if you're doing this for certain rows, you'll have to keep track of the
heights someway. And it seems like a very basic (in a good way) to store that
information in that row in a dedicated column (maybe hidden??). (I've always
found updating a bunch of cells in a worksheet much simpler than fixing an array
of values in a module--especially when things can change.)

Seems like an ok approach to me.
 
Top