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

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.

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


spencer wrote:

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

--
spencer

--

Dave Peterson

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

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.



Dave H wrote:

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

Dave Peterson Wrote:
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


spencer wrote:

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

--
spencer

--

Dave Peterson

--
Dave H

--

Dave Peterson