Solved

Auto size cells

Posted on 2014-04-02
14
277 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Maybe this will help.
truncated-cells.jpg
0
 
LVL 19

Expert Comment

by:Patricksr1972
Comment Utility
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
Comment Utility
*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
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 15

Author Comment

by:ericpete
Comment Utility
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 80

Expert Comment

by:byundt
Comment Utility
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
Comment Utility
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 80

Accepted Solution

by:
byundt earned 500 total points
Comment Utility
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 80

Expert Comment

by:byundt
Comment Utility
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
Comment Utility
*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
Comment Utility
Amazing. Simply amazing.

Thank you, Brad!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

771 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

12 Experts available now in Live!

Get 1:1 Help Now