[RESOLVED] VB6 - Merge Cells in spreadsheet

[westconn1]

Dim s as Worksheet, a as long (or integer),c as range, w as long,
no need to set c when used as a for each, had to do that way as you can't get the width of the range of more than one cell
looks like i copied it wrong as the range is hard coded, but should be the same as the working range, but as all the cells are the same width it works anyway, so should be moved outside (before) the loop to improve speed

certainly you can populate the sheet first, then use this code to format it
as you already have a sheet set (xlWksht) you don't need to use or set s as it is only a duplicate

here is the code incorporated into your original code
note i havn't tested this as i can't, not have the database, but it looks right, not i moved the code to set the width of column b to the top as it would affect the width of the comment text if it is changed afterwards

ii = 5
    xlWksht.Columns("B:B").ColumnWidth = 23
    For Each c In xlWksht.Range("A8:h8"): w = w + c.ColumnWidth: Next
    Do Until rsin.EOF = True
       ii = ii + 2
       xlWksht.Cells(ii, 1).Value = rsin![Req No]
       xlWksht.Cells(ii, 2).Value = rsin![Description]
       xlWksht.Cells(ii, 3).Value = rsin![P L] & Chr(10) & rsin![Pgmr2] & Chr(10) & rsin![Pgmr3]
       xlWksht.Cells(ii, 4).Value = rsin![ClientName] & Chr(10) & rsin![Status]
       xlWksht.Cells(ii, 5).Value = "-" & Chr(10) & rsin![Per Hrs]
       xlWksht.Cells(ii, 6).Value = rsin![Hours] & Chr(10) & rsin![Tot Hrs]
       xlWksht.Cells(ii, 7).Value = rsin![Start Date] & Chr(10) & rsin![Start Date]
       xlWksht.Cells(ii, 8).Value = rsin![End Date] & Chr(10) & rsin![End Date]
       xlWksht.Cells(ii + 1, 1).Value = "Comments:" & Chr(10) & "'" & rsin![Comments]
       With xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8))
              .HorizontalAlignment = xlLeft
              .VerticalAlignment = xlTop
              .WrapText = True
              .Orientation = 0
      '        .AddIndent = False
              .IndentLevel = 0
      '        .ShrinkToFit = False
              .MergeCells = True
              .RowHeight = .RowHeight * Len(xlWksht.Range("A" & a).Text) / w
      End With
 
       'xlWksht.Cells(ii + 1, 1).Value = Replace("Comments:" & Chr(10) & "'" & rsin![Comments], vbLf, vbTab) ' replace linefeed with tab
'''       xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).Merge
'''       xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).HorizontalAlignment = xlLeft
'''       xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).WrapText = True
'''       'xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).RowHeight = 70.25
'''       xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).VerticalAlignment = xlTop
'''       xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).Orientation = 0
       rsin.MoveNext