I'm creating a report in Excel from VB6, does anyone know if you can autofit merged cells? If so, please send an example my way.

Thanks,

Matt

Here's some code that does the trick. It first sets the width of the first column to the total of the merged cells, pastes the text into autofit set cells, then removes the autofit and marges the columns as required.

TWidth = 0: ROffset = 100 ' ROffset sets othe start row of the pasted text.
For Each col In Columns("A:E") ' get total width of cells to be merged
    TWidth = TWidth + col.ColumnWidth
    Next col
OldWidth = Columns("A").ColumnWidth
Columns("A").ColumnWidth = TWidth
Range("A1:A50").Offset(ROffset, 0).WrapText = True
Range("A1:A50").Offset(ROffset, 0).Rows.AutoFit
ThisWorkbook.Sheets("Reference Text").Range("A1:A50").Copy Destination:=Range("A1").Offset(ROffset, 0)
For Each rw In Selection.Rows ' set row heights to match auto fit & remove autofit
    rw.RowHeight = rw.RowHeight
Next rw
Range("A1").ColumnWidth = OldWidth ' restore first column's width
For i = 0 To 50: Range("A1:E1").Offset(ROffset + i, 0).MergeCells = True: Next i ' merge target cells