How to limit long text to column width?

[curiousgeorge408]

I have a VBA function that returns a very long string. If the
adjacent cell is blank, the display of the string extends past the
cell boundary. If the adjacent is nonblank, the display of the string
stops at the cell boundary.

How can I get the latter behavior even when the adjacent cell is
blank?

I don't want to truncate the string value, just the display of it.
That is, LEFT() is not a solution for me.

 

[pinmaster]

Hi,

Formatting the cells to "wrap text" should do!

Regards!
Jean-Guy

 

[curiousgeorge408]

Formatting the cells to "wrap text" should do!

Thanks for the response. But that is not what I want. That wraps the
text, normally increasing the row height to display the entire string,
still. I want the display of the string truncated at the cell
boundary -- as I wrote before, just like what happens when the
adjacent cell is nonblank.

Well, I 'spose I could change the row height back to its size before
setting "wrap text". But that requires forethought (or undo/redo) to
determine the pre-reformat row height. And it is a multistep process,
the number of steps depending on how many mistakes I make <wink>. It
gets especially messy if I have to do this in a large number of rows
with varying heights.

Sigh, I 'spose I could create a yet-another macro. Admittedly, not
difficult to do.

If "wrap text" is the best answer, so be it. But I am hoping for
something a little more straight-forward.

 

[Jean-Guy]

Hi,

I have excel 2000 and it works just fine, but if I format the row to "auto
fit" then yes it will adjust the row height to show the entire text, try
formatting your rows to a specific height see if that helps!

Regards!
Jean-Guy

 

[Jim Cone]

You can check each adjacent cell and if empty add an apostrophe
or a space in it....
If len(rCell.Offset(0, 1).Value) = 0 then rCell.Offset(0, 1).Value = "'"
Or you could use SpecialCells and find all blanks in the adjoining
column and add the same.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



<[email protected]>
wrote in message
I have a VBA function that returns a very long string. If the
adjacent cell is blank, the display of the string extends past the
cell boundary. If the adjacent is nonblank, the display of the string
stops at the cell boundary.

How can I get the latter behavior even when the adjacent cell is
blank?

I don't want to truncate the string value, just the display of it.
That is, LEFT() is not a solution for me.

 

[curiousgeorge408]

I have excel 2000 and it works just fine, but if I format the row to "auto
fit" then yes it will adjust the row height to show the entire text

First, sorry, I neglected to say that I have Office Excel 2003. Based
on your observation, that might make a difference. I do not see an
option or operation to "auto fit" rows per se. But when I select
"wrap text" for the cell format, the row height is changed
automatically. I do not see any way to disable that, except....

try formatting your rows to a specific height see if that helps!

Yes. But in the posting to which you responded, I explained my
reservations about doing that, namely: "It gets especially messy if I
have to do this in a large number of rows with varying heights".
Again, if that's the only way, so be it. I am hoping someone will
know a more straight-forward way.

Nonetheless, thanks for your thoughts.

 

[RagDyeR]

If you select your column and format it to "wrap text",
Auto-Fit is *automatically* turned on.

It (Auto-Fit) will turn itself off if the row height is manually changed.
In fact, many of the question in these groups have been on how to turn it on
again, after it was unintentionally turned off. (<Format> <Row> <Auto-Fit>)

For you, simply select the range you intend to use by clicking on the *row*
headers and dragging down, and then change the default row height. from
12.75 to say 13 or 12.5, and you'll see that the Auto-fit will be off for
those selected rows.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

I have excel 2000 and it works just fine, but if I format the row to "auto
fit" then yes it will adjust the row height to show the entire text

First, sorry, I neglected to say that I have Office Excel 2003. Based
on your observation, that might make a difference. I do not see an
option or operation to "auto fit" rows per se. But when I select
"wrap text" for the cell format, the row height is changed
automatically. I do not see any way to disable that, except....

try formatting your rows to a specific height see if that helps!

Yes. But in the posting to which you responded, I explained my
reservations about doing that, namely: "It gets especially messy if I
have to do this in a large number of rows with varying heights".
Again, if that's the only way, so be it. I am hoping someone will
know a more straight-forward way.

Nonetheless, thanks for your thoughts.

 

[curiousgeorge408]

I neglected to say that I have Office Excel 2003. Based on
your observation, that might make a difference. I do not see
an option or operation to "auto fit" rows per se.

Of course, I was wrong. Format / Rows / Auto Fit does exist. I
overlooked it because the Format menu was not fully expanded.

Oh well, based on the responses, it is clear that there is no simple
method <sigh>.