make row height automatic

[spencer]

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]

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]

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

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.


--
Dave H

[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

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]

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