Data to big for cell

[Andy]

Hi - I am programmatically setting the value of a particular cell in a
worksheet to a long string (some 2000 characters). I have set the width of
the column containing the cell to a sensible width (.ColumnWidth = 40) and I
have set the attribute .WrapText = True

I would expect to see all the data but some is not visible. The only way to
see the data is to later drag the column much wider etc. Surely, the wrap
attribute should suffice?

I have also tried inserting Char(10) at intervals within the string to act
like Ctrl +Alt (carriage throw) but no luck.

Any thoughts?

 

[Howard31]

Try manually enlarging the row size

 

[Bernard Liengme]

Try moving to point within the text and 'type'ALT+ENTER to wrap the text
best wishes

 

[Andy]

Needs to be done programmatically, not manually, and I do not know how long
the data will be until run time so would not know how large to make row size.
Would .Rows.AutoFit look after the row size?

 

[Andy]

Thanks Bernard but 'moving to point', is not really 'programmatically'.
I have tried inserting Char(10) already (as I said) which equates to ALT +
ENTER but still, the last part of text is not visible.

 

[Dave Peterson]

Did you autofit the rowheight?

If you autofit the rowheight manually, does it work ok?

If no, then if you increase the rowheight manually to what you need, do you see
all the characters in the cell? If you do, then just set that rowheight to what
you need.

 

[Howard31]

To autofit the row programmatically use Worksheets("Sheet1").Rows(1).AutoFit

 

[Andy]

Just to push the discussion on as this really is not easy to do. If you copy
this code into an excel worksheet function and run it it should set up 2001
characters of data in A1. Can anyone, (programmatically only), get the 'Y' on
the end of the data to be visible. I have tried various methods including
Chr(10) in the code below :-

Dim LongStr As String
Dim LongCheck, LongLimit As Integer
Dim ii As Integer

LongLimit = 100

For ii = 1 To 2000
LongStr = LongStr & "X"

LongCheck = Len(LongStr)
If LongCheck >= LongLimit Then
LongStr = LongStr & Chr(10)
LongLimit = LongLimit + 100
End If
Next ii

LongStr = LongStr & "Y"

With ActiveSheet
.Cells(1, 1).Value = LongStr

.Columns("A:A").ColumnWidth = 50
.Rows("1:1").AutoFit
.Cells(1, 1).WrapText = True
.Cells(2, 1).Select

 

[Howard31]

Put the Autofit statement before the WrapText statement

 

[Dave Peterson]

Sometimes when the amount of text in the cell is large, even autofitting the
rowheight won't help.

Specifically set the rowheight (record a macro to see the height you should use)

This worked fine for me in xl2003 using an Arial 10 font (and for my printer).

Dim LongStr As String
With Application
LongStr = .Rept(.Rept("X", 100) & vbLf, 20) & "Y"
End With
With Worksheets.Add
.Range("A1").Value = LongStr
.Columns(1).ColumnWidth = 255
.Columns(1).AutoFit
.Rows(1).RowHeight = 270.75
End With

But depending on your printer, font, etc, it may not work for you. That's why I
still recommend you record a macro when you do it manually.

And then if you have to share with someone else, you may want to build some
fudge into your numbers.