Changeable Row sizes to fit in the text!

K

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.....
 
R

raypayette

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

HTH
 
R

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 you
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
 
R

raypayette

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

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?
 
R

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 automaticall
expands and allows for word wrap!
Check that you don't use an Autoformat or Style that override thes
commands
 
K

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?
 
R

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!
 
K

Kazuki

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

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
 
G

Gord Dibben

kazuki

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

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

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

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


raypayette said:
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!
Click to expand...

Gord Dibben MS Excel MVP
Click to expand...
 
K

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 said:
kazuki

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

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

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
Click to expand...
 
K

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???????
 
Top