Printing truncates cell contents

[BDT]

I have a problem printing a simple spreadsheet. I have a single column
spreadsheet with several hundred rows. The cells contain text anywhere from
a few words to over a thousand words. Some of the longer entries have
paragraphs with CR/LF entered via alt+enter. I have the cells formated for
word wrap and then I double clicked on the bottom edge of one row which set
each row height just big enough to hold the cell contents. I have set a thin
border around each cell and everything looks good onscreen.

The problem is when I print, some of the cells do not display all of the
lines of text. I can manually stretch the height of each of these rows
separately, trial and error, to make them long/tall enough to print
correctly, but then there are blank lines that show up on screen. Not a real
problem, but with several hundred rows, not a very practical solution, plus
every cell edit causes a repeat of the trial and error process.

Any suggestions.

thanks in advance

 

[BDT]

BTW, I am using Excel 2002 on a Windows XP machine.

thanks

 

[Dave Peterson]

Maybe you can include an extra alt-enter as the final character in the cell?

 

[BDT]

Hi Dave,

Your idea works - sort of. If I only had a couple of easy to identiry
problem cells, your idea would be OK.

If I do that for one of the problem cells, it prints out OK, but it adds a
blank line at the end of the text on the screen so it doesn't display
onscreen correctly. Since there are many hundred rows I would need to find a
way to apply this to all cells, but then the rows that print out fine now,
print with an extra blank line at the bottom of the cell.

So I'm still stumped.

thanks, BDT

 

[Dave Peterson]

I think it's either going to be a manual effort (row by row) if you hate the
extra line within the cell for the rows that currently print ok.

One of the things that would concern me is that the paper copy may look fine on
my printer, but if I share it with others, it may not look fine for them.

If you decide to add that extra alt-enter to just the cells you want, you could
select the range first and use a macro to do the work.

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeConstants, xlTextValues), _
ActiveSheet.UsedRange)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Try another selection!"
Exit Sub
End If

For Each myCell In myRng.Cells
If Right(myCell.Value, 1) = vbLf Then
'already there, so skip this cell
Else
myCell.Value = myCell.Value & vbLf
End If
Next myCell

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[BDT]

Hi Dave,

Giving this some more thought, I came up with an alternative, brute force
solution that seems reasonable. I just cut and pasted the entire column from
my spreadsheet and pasted it into MS Word. After a couple of trial and error
attempts to tweak the width of the Excel column, it pasted right in to Word
and seems to display pretty well. There are a few cells with an extra blank
line at the bottom, but all-in-all a workable way to output the hundreds of
cells. To avoid other printer problems, I can even output the Word doc as a
pdf and anyone using it will see the same thing.

I'm guessing this is a bug in xl, but at least I can move on.

thanks again, BDT

 

[Dave Peterson]

If you're not using excel for anything that's "excel-ish", then to be able to
use all the word processing tools of MSWord sounds like a much better idea.