[RESOLVED] VB6 - Merge Cells in spreadsheet

[Giftx]

Hi Westconn1,
I tweaked my module using your codes. I got an output similar to what I'm looking for. However, I want the "comment" fields to wrap around (wrapText), left-justified and all the data to be seen both at display and when printed. Please take a close look of the tweaks I made.

Also, I see you hard-coded the number of rows in the "For" Loop
"For i = 1 to 105". The problem here is that the rows(number of records) is not fixed. It various depending on what the underlying query produces.

So can we make the number of records in the above "For" Loop dynamic (To depend on the number of rows with data)

See attachment of the current output.

 ii = 5
    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]
       
       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)).WrapText = True
       
       xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).EntireRow.AutoFit
       
       xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8)).HorizontalAlignment = xlLeft
       
       rsin.MoveNext
    Loop
    
    xlApp.Visible = True
    
    For i = 7 To 105
        Cells(i, 1) = Replace(Cells(i, 1), vbLf, vbTab)
    Next

Have a great weekend and a million thanks for all the help.
Same goes for you "Anhn".
GiftX