Number of word wraps (excel)

[jason246day]

I am trying to find out how many times a text entry in a cell word wraps. I need to know that so I can automatically adjust the cell height to compensate. I could do it by counting characters, but some of the cells are indented. Is there a way to count how many times a sentence wraps around?

 

[SkipVought]

What you do is test the rowheight under the current columnwidth condition.

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[jason246day]

Could you explain a little more, I am a programmer, but I just don't know the vba calls, and am having a hard time locating them on the web.

 

[SkipVought]

what you could do is use the autofit method.

Turn on your macro recorder, dowble click the ROW BOUNDARY if the row containing the wrapped text and the next row.

turn off the macro recorder and observe the code. A very good way to LEARN VBA.

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[jason246day]

I've already done that, the problem with AutoFit is that it expands the width.
I am creating a printer friendly version of my data. If the columns are all set to default, then my text cannot go past than column I, or it won't print on a single page. So I merge the cells to I on each row, and turn on word wrap. The problem I run into is that some of the lines are longer than the space provided, so I need to adjust the height on those, so that those lines will be seen. Does this make sense?

 

[SkipVought]

"...so I can automatically adjust the cell height to compensate. "

"...the problem with AutoFit is that it expands the width."

Ya can't have it both ways, pal!



Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[jason246day]

Yes I can, I have it working. I just need to figure out the lengths of the cells, Len(Cells(row, col)), at which I want it to wrap. Because of the indentions, not all of the cells are the same width.

Do you know if there is a standard length for a defualt sized cell. I tried 10, and then multiplied that out for the number of cells that it covers.

 

[SkipVought]

The LEN function return the number of CHARACTERS in the cell, NOT the ColumnWidth.

What is the Default ColumnWidth?

Insert a new sheet.

Select a column.

Right Click and select Column Width.

If you have merged cells, the cell width is the sum of the ColumnWidths of the columns merged.


Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[PHV]

If you use a monospaced (fixed) font, then the number of characters give you a string width.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244