Keep cell size

[Guest]

Hi, I am using VBA to query a database and then putting data into cells.
Some values have really long text in them and excel automatically turning on
wordwrap for some of them which results is very large cells in some places
which throws off the view of the worksheet. Is there any way to prevent
excel from doing this. i dont mind not being able to see all of the text.
Also, is there anyway to prevent excel from spilling long text into the next
column if the next column is blank? Thanks

Mark

 

[Jim Rech]

You cannot change how Excel behaves but you can turn off word wrap after the
query. A macro can make this quick and easy.

Yes, leave on word wrap<g>. Oh you want to eat your cake and have it too?
Well I'd leave word wrap on and then change the row height manually (or by
macro) to one line's height.

--
Jim
| Hi, I am using VBA to query a database and then putting data into cells.
| Some values have really long text in them and excel automatically turning
on
| wordwrap for some of them which results is very large cells in some places
| which throws off the view of the worksheet. Is there any way to prevent
| excel from doing this. i dont mind not being able to see all of the text.
| Also, is there anyway to prevent excel from spilling long text into the
next
| column if the next column is blank? Thanks
|
| Mark

 

[Dave Peterson]

Does your text have linefeeds in it, like:

ActiveCell.Value = "hello" & vbLf & "goodbye"

Excel turned on wordwrap when I did this.

And if you have wordwrap already toggled on, xl adjusted the rowheight.

One way to avoid either situations is to check (and save the rowheight's value),
then plop your data in and then adjust the rowheight back to what it was.

Excel will also refrain from adjusting rowheights if you've changed the row's
height (not by autofitting).

myHeight = ActiveCell.EntireRow.RowHeight
ActiveCell.Value _
= "hello goodbye hello goodbye hello goodbye hello goodbye"
ActiveCell.EntireRow.RowHeight = myHeight


And the only way I know that you can stop excel from filling the adjacent cell
with long text is to either turn wrap text on--or put something in that adjacent
cell.

I like to use a formula:
=""

I find it easier to see in the formula bar (than a space character) and it's
different enough to remind me why it's there.