"joel" wrote:
Multirow only applies the controls and not the worksheet.
Use wrap text to the worksheet.
Range("A1").WrapText = True

I had high hopes for this suggestion because it rang true.

But setting WrapText did nothing.

On the off-chance I did something wrong, see the complete modified macro
below. If you have a correction, please snip and correct the lines from the
macro to be specific. And please test your suggestion. I couldn't have
made it any easier to do.

I tried setting WrapText a few different ways separately. Again, none
worked for me.

First, after the loop (my preference):

Range(cellName).Resize(1, nCol).Columns.WrapText = True
Range(cellName).Resize(1, nCol).Columns.AutoFit

Note: I also altered the AutoFit line, changing Resize(nRow,nCol) to
Resize(1,nCol). I think the latter is sufficient. But I tried it both
ways, to no avail.

Alternatively, in the loop:

Range(cellName).Cells(r, c).WrapText = True
Range(cellName).Cells(r, c) = s

Finally, alternatively, in the loop:

Range(cellName).Cells(r, c).NumberFormat = "@"
Range(cellName).Cells(r, c).WrapText = True
Range(cellName).Cells(r, c) = s


The modified macro....


Option Explicit
#Const multirow = True
#Const kludge1fit = False
#Const kludge2fit = False

Private Sub doit()
Const cellName As String = "B2"
Const nRow As Long = 3
Const nCol As Long = 3
Dim r As Long, c As Long, i As Long, t As String, s As String
Dim maxLen As Integer, x As Long

Range("a:z").Delete
Range("a1").Select

#If kludge1fit And Not kludge2fit Then
'set ColumnWidth before storing text
Range(cellName).Resize(1, nCol).ColumnWidth = 100
#End If

For r = 1 To nRow: For c = 1 To nCol
t = String(r * c, "x")
s = "morelongtext1" & t
#If multirow Then
For i = 2 To r * c
s = s & Chr(10) & "morelongtext" & String(i, 48 + i) & t
Next i
#End If
Range(cellName).Cells(r, c).NumberFormat = "@" 'workaround
Range(cellName).Cells(r, c).WrapText = True 'workaround
Range(cellName).Cells(r, c) = s
Next c: Next r

#If kludge2fit Then
'set ColumnWidth after storing text
x = nRow * nCol
maxLen = Len("morelongtext" & String(x, 48 + x) & t)
Range(cellName).Resize(1, nCol).ColumnWidth = maxLen
#End If

Range(cellName).Resize(1, nCol).Columns.AutoFit
End Sub