Wrap text

[Fonty]

Suppose you have a cell with the following value: "Line one and line 2". You set the format of the cell to wrap text and you change its size so that it looks like:

Line one and
line 2

Is it possible in vba to extract tomething like "Line one and" & vbLine & "line 2"?
I think you can somehow deduce how many rows the cell has if you use my_rows=round(cell(1,1).height / cell(1,1).font.size, 0) so you can actually know how many vbLine to use. But still it's incomplete...

[westconn1]

i played with this, but it is not correct

Dim r As Range, myarr() As String
Set r = Range("A3")
linecnt = Len(r) \ r.ColumnWidth
ReDim myarr(linecnt)
awords = Split(r)
tmp = awords(0) & " "
j = 0
For i = 1 To UBound(awords)
    If Len(tmp) + Len(awords(i)) > r.ColumnWidth + 1 Then
        myarr(j) = tmp
        j = j + 1
        tmp = awords(i) & " "
    Else
        tmp = tmp & awords(i) & " "
    End If
Next
myarr(j) = tmp

unfortunately vba has no objects with textwidth property to work with, so i do not know anyway to get the accurate width of a string
also excel will wrap words at point where there is no space if the word is too long and i did not test for that, though it could be done

[westconn1]

i have been able to make this work, but it will return the lines as displayed in cells edit mode may change slightly after leaving the cell

requires use of activex dll