Solved

Auto size cells

Posted on 2014-04-02
14
278 Views
Last Modified: 2014-04-02
I'm inserting short paragraphs into cells (copied from a text editor -- so no URLs, no special characters). The cells are set to word wrap, and I've tried changing the vertical alignment, but that has had no effect.

The problem: some of the cells resize automatically and display the text. Others in the same column do not.

I know I can resize the rows, but that seems kind of clunky. Is there a secret setting that will automatically increase the row height as necessary?

I've tried Brad's solution from http://www.vbaexpress.com/kb/getarticle.php?kb_id=421 but while it did set the text to a specific WIDTH, it didn't change the row height.
0
Comment
Question by:ericpete
  • 7
  • 4
  • 3
14 Comments
 
LVL 19

Expert Comment

by:Patricksr1972
ID: 39973161
Hi

Why not select the whole spreadsheet and double click the height separator and all rows are set to Heights that are fitting.

select all Double click seperator
0
 
LVL 15

Author Comment

by:ericpete
ID: 39973182
I should have said... this is Office 2000... so I don't know where I'd find the "height separator"... Even "auto-fit"ting the row height doesn't have an effect.
0
 
LVL 19

Expert Comment

by:Patricksr1972
ID: 39973189
Wow it has been some time since i last saw Office 2000, isnt there a line between rows 1 and 2 like the images?
0
 
LVL 15

Author Comment

by:ericpete
ID: 39973223
Maybe this will help.
truncated-cells.jpg
0
 
LVL 19

Expert Comment

by:Patricksr1972
ID: 39973291
Yes that helps...

So first click where the blue arrows points to to select the whole sheet.

Next double click any line between two rows. In my example i point to the line between rows 158 and 159. Double clicking this line while all selected it should 'autoheight' for all rows.
There is a seperator line
0
 
LVL 15

Author Comment

by:ericpete
ID: 39973344
*grumbling*

After step 1, when I get directly on the line separating the two rows, I get the icon similar to the one you get when you widen a column (except the arrows point up and down rather than to both sides). Double-clicking does nothing; dragging the line down makes all of the cells taller (as expected), but doesn't resolve the truncating issue -- even if I increase the row to more than enough vertical room.

If I'm slightly off the line, I see a white plus sign, and even clicking once selects whatever row it is.
0
 
LVL 19

Expert Comment

by:Patricksr1972
ID: 39973361
Hi

The icon you see is what i would expect, maybe the double clicking is not present in 2000.
After step 1, if you click 'format' -> Row autofit, does this help?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 15

Author Comment

by:ericpete
ID: 39973393
No effect at all.

I did find that, when entering the text into a cell, if I select the cell below the one I'm entering data into AND the lower cell is empty, then it resizes.
0
 
LVL 81

Expert Comment

by:byundt
ID: 39973976
This is one of those problems where it would be desirable to see the actual workbook.

FWIW, merged cells prevent the AutoFit from working. In looking at your screenshot, I see four columns of data, starting with column A and continuing through column G. Either you have hidden some columns, or you have merged some cells.

Also, the row height cannot exceed 409.5 points, and Excel 2000 doesn't promise to display more than about 1200 characters as I recall.
0
 
LVL 15

Author Comment

by:ericpete
ID: 39974006
I've not merged any cells, and in the screen shot, several columns were hidden.

I don't think many of the cells that display XXXXX....XXXX have 1200 characters, and I can't see anything in any of them that should result in the word-wrap not working.

But... ask and ye shall receive...
28403560.xls
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39974011
Change the cell format to General. When you format it as Text, you get the annoying ###### if the cell contains more than 255 characters.

Brad
0
 
LVL 81

Expert Comment

by:byundt
ID: 39974017
If you want to test whether any of the cells contain too many characters, put the following formula in cell G1:
=LEN(F1)

Then doubleclick the little square at bottom right of the selection marquee surrounding cell G1. That will copy the formula down to the first blank cell in column F.
0
 
LVL 15

Author Comment

by:ericpete
ID: 39974040
*laughing hysterically*

The longest description -- by a significant margin --  is from some guy who managed to come up with 1199 characters on Microsoft Excel...
0
 
LVL 15

Author Closing Comment

by:ericpete
ID: 39974041
Amazing. Simply amazing.

Thank you, Brad!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now