VB6 - Spredsheet - Some line of text in cell not visible when printed

[tgifgemini]

Hi Members,
I am directly referring to you because you have firsthand idea of what I went through developing this little piece of vb apps. which produces an excel spreadsheet.
However, the program is working perfectly okay, but when I make the spreadsheet visible or when I print the spreadsheet, some of the text is not visible. You can see that half of the line of text is cut off (you can only see the top half of the characters) Does anyone know why this is and how I can fix it?
See attachment for the spreadsheet.
Thanks,
tgifgemini

[QVeen72]

Hi tgif,

For each row write this :

Rows("14:14").EntireRow.AutoFit

Change the 14:14 to 1:1 and so on...

Regards
Veena

[tgifgemini]

Hi Veena,
sorry but I'm not quite clear with your direction. Could you be a little precise.

See my entire module where I'm doing my processing. Like I said, it's working okay. It's just the display issue as you saw on the attachment.

ii = 5
     w = 0
    For Each R In xlWksht.Range("A8:h8"): w = w + R.ColumnWidth: Next
    
    rht = xlWksht.Range("A6").RowHeight
    
    Do Until rsinPers.EOF = True
       ii = ii + 2
       xlWksht.Cells(ii, 1).Value = rsinPers![Req No]
       xlWksht.Cells(ii, 2).Value = rsinPers![Description]
       xlWksht.Cells(ii, 3).Value = rsinPers![P L] & Chr(10) & rsinPers![Pgmr2] & Chr(10) & rsinPers![Pgmr3]
       xlWksht.Cells(ii, 4).Value = rsinPers![ClientName] & Chr(10) & rsinPers![Status]
       xlWksht.Cells(ii, 5).Value = "-" & Chr(10) & rsinPers![Per Hrs]
       xlWksht.Cells(ii, 6).Value = rsinPers![Hours] & Chr(10) & rsinPers![Tot Hrs]
       xlWksht.Cells(ii, 7).Value = rsinPers![Start Date] & Chr(10) & rsinPers![Start Date]
       xlWksht.Cells(ii, 8).Value = rsinPers![End Date] & Chr(10) & rsinPers![End Date]
       xlWksht.Cells(ii + 1, 1).Value = "Comments:" & Chr(10) & "'" & xlApp.Clean(Trim(rsinPers![Comments]))
          
       With xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8))
            .HorizontalAlignment = xlLeft
            .VerticalAlignment = xlTop
            .WrapText = True
            .Orientation = 0
            .IndentLevel = 0
            .MergeCells = True
            .RowHeight = rht * Len(xlWksht.Range("A" & ii + 1).text) / w
       End With
       rsinPers.MoveNext
    Loop

Thanks,
tgifgemini

[QVeen72]

Hi,

just before rsinpers.MoveNext, give this code:

Dim RowName as String
Dim LNo as long
LNo = ii
RowName = CStr(LNo) & ":" & CStr(LNo) 
Rows(RowName).EntireRow.AutoFit
LNo = LNo+1
RowName = CStr(LNo) & ":" & CStr(LNo)
Rows(RowName).EntireRow.AutoFit

Dim the variables outside the loop.
Another way is to first populate all then data. and after that againg Loop thru all the used Row Range and auto fit each row...

REgards
Veena

[tgifgemini]

Good morning Veena.
I have modified with your code sample. I will give you a feedback after I step thru it.
Meanwhile, see my module below:

Dim RowName As String
    Dim LNo As Long
    
    Do Until rsinPers.EOF = True
       ii = ii + 2
       xlWksht.Cells(ii, 1).Value = rsinPers![Req No]
       xlWksht.Cells(ii, 2).Value = rsinPers![Description]
       xlWksht.Cells(ii, 3).Value = rsinPers![P L] & Chr(10) & rsinPers![Pgmr2] & Chr(10) & rsinPers![Pgmr3]
       xlWksht.Cells(ii, 4).Value = rsinPers![ClientName] & Chr(10) & rsinPers![Status]
       xlWksht.Cells(ii, 5).Value = "-" & Chr(10) & rsinPers![Per Hrs]
       xlWksht.Cells(ii, 6).Value = rsinPers![Hours] & Chr(10) & rsinPers![Tot Hrs]
       xlWksht.Cells(ii, 7).Value = rsinPers![Start Date] & Chr(10) & rsinPers![Start Date]
       xlWksht.Cells(ii, 8).Value = rsinPers![End Date] & Chr(10) & rsinPers![End Date]
       xlWksht.Cells(ii + 1, 1).Value = "Comments:" & Chr(10) & "'" & xlApp.Clean(Trim(rsinPers![Comments]))
          
       With xlWksht.Range(Cells(ii + 1, 1), Cells(ii + 1, 8))
            .HorizontalAlignment = xlLeft
            .VerticalAlignment = xlTop
            .WrapText = True
            .Orientation = 0
            .IndentLevel = 0
            .MergeCells = True
            .RowHeight = rht * Len(xlWksht.Range("A" & ii + 1).text) / w
            'xlWksht.Rows("14:14").EntireRow.AutoFit
       End With
       
       LNo = ii
       RowName = CStr(LNo) & ":" & CStr(LNo)
       Rows(RowName).EntireRow.AutoFit
       LNo = LNo + 1
       RowName = CStr(LNo) & ":" & CStr(LNo)
       Rows(RowName).EntireRow.AutoFit
       
       rsinPers.MoveNext
    Loop

Thanks,
tgifgemini.