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?

Try manually enlarging the row size

"Andy" <(E-Mail Removed)> wrote in message
news:C9F386F5-549F-4AA5-919D-(E-Mail Removed)...
> 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?

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

--
Bernard Liengme
MVP Excel
http://people.stfx.ca/bliengme

"Andy" <(E-Mail Removed)> wrote in message
news:C9F386F5-549F-4AA5-919D-(E-Mail Removed)...
> 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?

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?

"Howard31" wrote:

> Try manually enlarging the row size
>
> "Andy" <(E-Mail Removed)> wrote in message
> news:C9F386F5-549F-4AA5-919D-(E-Mail Removed)...
> > 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?
>
>

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.

"Bernard Liengme" wrote:

> Try moving to point within the text and 'type'ALT+ENTER to wrap the text
> best wishes
>
> --
> Bernard Liengme
> MVP Excel
> http://people.stfx.ca/bliengme
>
> "Andy" <(E-Mail Removed)> wrote in message
> news:C9F386F5-549F-4AA5-919D-(E-Mail Removed)...
> > 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?
>

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.

Andy wrote:
>
> 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.
>
> "Bernard Liengme" wrote:
>
> > Try moving to point within the text and 'type'ALT+ENTER to wrap the text
> > best wishes
> >
> > --
> > Bernard Liengme
> > MVP Excel
> > http://people.stfx.ca/bliengme
> >
> > "Andy" <(E-Mail Removed)> wrote in message
> > news:C9F386F5-549F-4AA5-919D-(E-Mail Removed)...
> > > 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?
> >

--

Dave Peterson

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

"Andy" <(E-Mail Removed)> wrote in message
news:C9F386F5-549F-4AA5-919D-(E-Mail Removed)...
> 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?

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

"Andy" wrote:

> 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?

Put the Autofit statement before the WrapText statement

"Andy" <(E-Mail Removed)> wrote in message
news:ED1F18C8-A479-4195-81E3-(E-Mail Removed)...
> 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
>
> "Andy" wrote:
>
>> 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?

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.



Andy wrote:
>
> 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
>
> "Andy" wrote:
>
> > 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?

--

Dave Peterson