Member Avatar for 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

New_xlspredsheet1_some_line_of_text_not_Visible.zip (52.08 KB)
  • 2 Contributors
  • 4 Replies
  • 113 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by tgifgemini

Dani AI

Generated

The clipping is almost always caused by using merged, wrapped cells. ’s comments row is merged across columns and, as pointed toward, Excel’s AutoFit can adjust a row — but it will not reliably compute the height for wrapped text inside a merged range. Manual length/division tricks based on character count are fragile because fonts are proportional, printers scale differently, and Excel’s wrapping logic counts pixels/points, not characters.

Practical options (ordered by reliability):

  • Avoid merging. Put the long text into a single cell (or use the alignment option "Center Across Selection") so Excel can AutoFit the row automatically.
  • If merging is required, use a temporary, unmerged helper cell to let Excel compute the height and then apply that height to the merged row:
    1. Copy the merged cell text to a spare single column cell on the same row.
    2. Set that helper column’s width equal to the total width of the merged area (or set it to the same point width).
    3. Turn WrapText on for the helper cell and let Excel AutoFit that row.
    4. Read the helper row’s RowHeight and apply it to the merged row.
    5. Clear the helper cell.
  • As an alternative for printable results, place the text in a text box/shape and autosize it; shapes can be positioned over the merged area and print consistently.

Troubleshooting checklist:

  • Ensure WrapText is set before letting Excel calculate height.
  • Use Print Preview and a PDF print driver to rule out printer driver scaling issues.
  • Verify VerticalAlignment is Top so the text sits at the top of the cell once the correct height is set.
  • When automating from VB/VBA, measure the computed RowHeight (in points) rather than relying on character counts; copy that value to the merged row.

This avoids the merged-cell AutoFit limitation and produces consistent on-screen and printed results.

Recommended Answers

All 4 Replies

Member Avatar for 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

Member Avatar for 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

Member Avatar for 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

Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.