[RESOLVED] VB6 - Merge Cells in spreadsheet

[westconn1]

Also, please look at the spreadsheet attachment. Is there any way I can get rid of the space(gap) that separates the line of text in the "Comment" row?

when i replaced the linefeed in the comments i replaced it with a tab, hence the gap, you can change the tab to whatever is suitable, a space or a comma etc.

as far as the line height when i tested it it worked properly so i don't know what the problem is now

try it like this

ii = 5
          xlWksht.Columns("B:B").ColumnWidth = 23
          For Each c In xlWksht.Range("A8:h8"): w = w + c.ColumnWidth: Next
         rht = xlWsht.Range("a6").RowHeight
 
          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 = rht * 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

the problem is that the rowheight is already oversize, in the row that contains the comments so rht stores the base sixe for the row height an calculates from that
also i might have miss copied something when i posted so make sure there are no other changes apart from the ones for the rowheight