Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Auto size cells

Posted on 2014-04-02
14
Medium Priority
?
295 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:Eric AKA Netminder
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
14 Comments
 
LVL 23

Expert Comment

by:Patrick Bogers
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:Eric AKA Netminder
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 23

Expert Comment

by:Patrick Bogers
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 15

Author Comment

by:Eric AKA Netminder
ID: 39973223
Maybe this will help.
truncated-cells.jpg
0
 
LVL 23

Expert Comment

by:Patrick Bogers
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:Eric AKA Netminder
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 23

Expert Comment

by:Patrick Bogers
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
 
LVL 15

Author Comment

by:Eric AKA Netminder
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:Eric AKA Netminder
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 2000 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:Eric AKA Netminder
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:Eric AKA Netminder
ID: 39974041
Amazing. Simply amazing.

Thank you, Brad!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

610 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