Autofit single cell XL03

[JayRedE]

Hi,
</PRE>

I am using XL03 & XP SP2.
</PRE>

I am trying to autofit rows, all of which one column has cells that contain anywhere from 0 - 1500 characters. No cells are merged on the worksheet. Each of these cells with content have manual linebreaks in them to designate seperate entries (each line contains an action completed on a certain date). These lines within a cell may contain 10 characters or 100. Autofitting works about 75% of the time, while the other 25% of the time it will cut off several lines at the bottom (without maxing out at a row height of 409). I have created a (admittedly crude) macro to calculate rowheight based on static font size (in this case) and an assumption that each line takes up ~75% of the column. Rowheight = (Amount of chars * determined character height ) / chars per columnThe problem with this is there is usually a fair bit of white space at the bottom of the cells (wasting paper), and in specialy circumstances could still cut of cells if many entries are short (ie take up less column space).Is there anyway to get autofit to work 100% of the time and not cut of rows (preferable) or a better method of "manually autofitting".
</PRE>
A couple other peices of info:
I have tried several different formatting options regarding vertical alignment, text style & font etc. I have even applied the same formatting to the entire worksheet to no avail.

It seems to affect cells randomly based on amount of characters, and length of individual entries. Longer cells 700+ seem to have this problem more, but shorter ones can have problems as well.
</PRE>

Here is a snippet from the code where the "autofitting" is done:
</PRE>

rowNum = 2
Do

'Determines if this row has information
emptyRow = IsEmpty(Range("A" & rowNum).Value)

'If this row contains information
If emptyRow = False Then

Rows(rowNum & ":" & rowNum).EntireRow.AutoFit

'Calculate amount of characters in cell
Range("M" & rowNum).FormulaR1C1 = "=LEN(RC[-1])"

'Theoretical row height with ~75% of column used up (55 units) and line height of 14 chars
rowHeightTest = (Range("M" & rowNum).Value * 14) / 40

'If theoretical number is greater than autofit number
If rowHeightTest > Rows(rowNum & ":" & rowNum).RowHeight Then

'Failsafe if theoretical number is greater than max.
If rowHeightTest > 409 Then rowHeightTest = 409
Rows(rowNum & ":" & rowNum).RowHeight = rowHeightTest

End If

'Clean up temp. cells and cycle to next cell
Range("M" & rowNum).ClearContents
rowNum = rowNum + 1
End If

Loop Until emptyRow = True
</PRE>


</PRE>

Feel free to ask and questions.
</PRE>

Thanks for your help,
</PRE>

Jay
</PRE>

 

[Mala]

Yes, I have noticed that the text refuses to wrap in the cell when the text length is in excess of (1024 + some small arbitrary number) of characters? This despite row height < 409. You may need to break your longsr text (1500+) into two cells.

 

[JayRedE]

Thanks Mala,

That was what I originally thought, but I have found instances where the character count is < 1000 (In an instance I just found the count was 872, and autofit still chops off a line). I will have my code kick in at 1000 characters to expand the selection further if I can isolate the problem to just ~1000 chars.

In this 872 example If i resize the column, sometimes autofit works and sometimes it doesn't. It doesn't appear to have a pattern as sometimes if I shrink the size it works, and sometimes I have to expand the column to make it work. No new lines are wrapping or un-wrapping. Any thoughts on this one?

I just developed a script that if my calculated row height is beyond 409, an appropriate percentage of the content in the cell in question will be moved temporarily to an inserted row below. Once the macro prints the report, it puts everything back the way it was. If I could be confident autofit would work properly (below 1024) I could just spill everything over ~1000 to the new cell.