Changeable Row sizes to fit in the text!

[Kazuki]

In my excel sheet, columns widths are fixed and cannot be edited. When data
is added, the row height would be changed and words are wrapped.

But the row height is not getting adjusted to show the text contents when i
double-clicked on the border of the row when the adjust symbol is shown.

What should I do since I cannot hand edit the row height for hundreds of
rows.....

[raypayette]

Put this code in each worksheet:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Rows("1:65536").EntireRow.AutoFit
End Sub

HTH

[Kazuki]

I am new to use excel. So I do not know where to use this code. Please help!

"raypayette" wrote:

>
> Put this code in each worksheet:
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Rows("1:65536").EntireRow.AutoFit
> End Sub
>
> HTH
>
>
> --
> raypayette
>
>
> ------------------------------------------------------------------------
> raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569
> View this thread: http://www.excelforum.com/showthread...hreadid=548858
>
>

[raypayette]

Call the Visual Basic Editor either by clicking Alt+F11 or
by clicking on the Tools, Macros, Visual Basic Editor.
You should see the Project Explorer; click Ctrl+R if you don't.
In the Microsoft Excel Objects there should be a list of your worksheets;
initially they should be named Sheet1, Sheet2 and Sheet3.
Click on each name and place the macro in each one.
Save the file and return to your Worksheet; press Alt+F11 to do this.

[raypayette]

I took it for granted that your cells would wrap text. To do this select the ranges, click Format, Cells, Alignment, and Wrap text.

[Kazuki]

I have already selected word wrap for the whole sheet.
I had already tried this option
Format --> Row --> Autofit
I guessed this should change the row height to display the whold text.
Rather this option is reducing the size to a normal row height and displaying
only 1 line of the wrapped text in the selected row....
The same happens when I use this macro in VBA rditor also.....
What should I do?

"raypayette" wrote:

>
> Call the Visual Basic Editor either by clicking Alt+F11 or
> by clicking on the Tools, Macros, Visual Basic Editor.
> You should see the Project Explorer; click Ctrl+R if you don't.
> In the Microsoft Excel Objects there should be a list of your
> worksheets;
> initially they should be named Sheet1, Sheet2 and Sheet3.
> Click on each name and place the macro in each one.
> Save the file and return to your Worksheet; press Alt+F11 to do this.
>
>
> --
> raypayette
>
>
> ------------------------------------------------------------------------
> raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569
> View this thread: http://www.excelforum.com/showthread...hreadid=548858
>
>

[raypayette]

Indeed it should work!
Check again that the cell' Word wrap option is selected.
When I do it, and I write overflowing data the row automatically expands and allows for word wrap!
Check that you don't use an Autoformat or Style that override these commands.

[Kazuki]

Actually it works in other excel sheets. Only in this sheet, this behavior is
there. So it must be something else that I have done in the sheet.
I tried to copy 1 part from this sheet to another sheet where autofit is
working, but there also this part is not fitting the text in the row. In the
beginnning of creating this excel sheet, I fixed row size to some number.
Could this be there reason?
Even if not, what else could be the reason and how can I overwrite it?

"raypayette" wrote:

>
> Indeed it should work!
> Check again that the cell' Word wrap option is selected.
> When I do it, and I write overflowing data the row automatically
> expands and allows for word wrap!
> Check that you don't use an Autoformat or Style that override these
> commands.
>
>
> --
> raypayette
>
>
> ------------------------------------------------------------------------
> raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569
> View this thread: http://www.excelforum.com/showthread...hreadid=548858
>
>

[raypayette]

Good.
Normally reformatting the rows with Autofit should cancel the fixed height row setting. Try this again.
If this method does not work, I would copy the values of worsheet to another worksheet using PasteSpecial, avoiding to copy the format.
This should work!

Best of luck!

[Kazuki]

Just now did I identified what was happening!
Seems like Autofit is not working for merged cells.... Any hopes of turning
this off?


"raypayette" wrote:

>
> Good.
> Normally reformatting the rows with Autofit should cancel the fixed
> height row setting. Try this again.
> If this method does not work, I would copy the values of worsheet to
> another worksheet using PasteSpecial, avoiding to copy the format.
> This should work!
>
> Best of luck!
>
>
> --
> raypayette
>
>
> ------------------------------------------------------------------------
> raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569
> View this thread: http://www.excelforum.com/showthread...hreadid=548858
>
>

[raypayette]

To turn off the merged cell, click Format, Cells, Alignment and click on the Merge Cell box to erase the check.

Another option would be to merge the rows rather than the columns and then to Autofit the columns!

HTH

[Gord Dibben]

kazuki

You can resize manually or via Macro. Jim Rech has written
code for this.

http://groups.google.com/groups?thre...%40tkmsftngp05

Also revised code for special cases.

http://snipurl.com/dfsm

The best cure is DON"T USE MERGED CELLS. They cause no end of problems with
copying, pasting, sorting, filtering.


Gord Dibben Excel MVP

On Mon, 12 Jun 2006 21:09:01 -0700, Kazuki <[email protected]>
wrote:

>Just now did I identified what was happening!
>Seems like Autofit is not working for merged cells.... Any hopes of turning
>this off?
>
>
>"raypayette" wrote:
>
>>
>> Good.
>> Normally reformatting the rows with Autofit should cancel the fixed
>> height row setting. Try this again.
>> If this method does not work, I would copy the values of worsheet to
>> another worksheet using PasteSpecial, avoiding to copy the format.
>> This should work!
>>
>> Best of luck!
>>
>>
>> --
>> raypayette
>>
>>
>> ------------------------------------------------------------------------
>> raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569
>> View this thread: http://www.excelforum.com/showthread...hreadid=548858
>>
>>

Gord Dibben MS Excel MVP

[Kazuki]

Thanks; Since I am have not used macros before, I am not able to figure out
what this macro does. I did the following:

ALT F11 --> inserted a module --> double clicked module1 --> pasted the code
in the window --> saved --> returned to excel sheet (ALT F11) --> ALT F8 -->
double clicked the macro I just saved......
Nothing is happening. What should Iactually do?

"Gord Dibben" wrote:

> kazuki
>
> You can resize manually or via Macro. Jim Rech has written
> code for this.
>
> http://groups.google.com/groups?thre...%40tkmsftngp05
>
> Also revised code for special cases.
>
> http://snipurl.com/dfsm
>
> The best cure is DON"T USE MERGED CELLS. They cause no end of problems with
> copying, pasting, sorting, filtering.
>
>
> Gord Dibben Excel MVP
>
> On Mon, 12 Jun 2006 21:09:01 -0700, Kazuki <[email protected]>
> wrote:
>
> >Just now did I identified what was happening!
> >Seems like Autofit is not working for merged cells.... Any hopes of turning
> >this off?
> >
> >
> >"raypayette" wrote:
> >
> >>
> >> Good.
> >> Normally reformatting the rows with Autofit should cancel the fixed
> >> height row setting. Try this again.
> >> If this method does not work, I would copy the values of worsheet to
> >> another worksheet using PasteSpecial, avoiding to copy the format.
> >> This should work!
> >>
> >> Best of luck!
> >>
> >>
> >> --
> >> raypayette
> >>
> >>
> >> ------------------------------------------------------------------------
> >> raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569
> >> View this thread: http://www.excelforum.com/showthread...hreadid=548858
> >>
> >>
>
> Gord Dibben MS Excel MVP
>

[Kazuki]

It works when the particular merged cell is selected and the macro is run.
It's not working when a cell range is selected. If only in a particular cell
it works, then it will not solve my purpose.
Is there any way to make this macro work for a range of cells???????

"Kazuki" wrote:

> Thanks; Since I am have not used macros before, I am not able to figure out
> what this macro does. I did the following:
>
> ALT F11 --> inserted a module --> double clicked module1 --> pasted the code
> in the window --> saved --> returned to excel sheet (ALT F11) --> ALT F8 -->
> double clicked the macro I just saved......
> Nothing is happening. What should Iactually do?
>
> "Gord Dibben" wrote:
>
> > kazuki
> >
> > You can resize manually or via Macro. Jim Rech has written
> > code for this.
> >
> > http://groups.google.com/groups?thre...%40tkmsftngp05
> >
> > Also revised code for special cases.
> >
> > http://snipurl.com/dfsm
> >
> > The best cure is DON"T USE MERGED CELLS. They cause no end of problems with
> > copying, pasting, sorting, filtering.
> >
> >
> > Gord Dibben Excel MVP
> >
> > On Mon, 12 Jun 2006 21:09:01 -0700, Kazuki <[email protected]>
> > wrote:
> >
> > >Just now did I identified what was happening!
> > >Seems like Autofit is not working for merged cells.... Any hopes of turning
> > >this off?
> > >
> > >
> > >"raypayette" wrote:
> > >
> > >>
> > >> Good.
> > >> Normally reformatting the rows with Autofit should cancel the fixed
> > >> height row setting. Try this again.
> > >> If this method does not work, I would copy the values of worsheet to
> > >> another worksheet using PasteSpecial, avoiding to copy the format.
> > >> This should work!
> > >>
> > >> Best of luck!
> > >>
> > >>
> > >> --
> > >> raypayette
> > >>
> > >>
> > >> ------------------------------------------------------------------------
> > >> raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569
> > >> View this thread: http://www.excelforum.com/showthread...hreadid=548858
> > >>
> > >>
> >
> > Gord Dibben MS Excel MVP
> >