Printing wrapped text

[stacyjean622]

I am having an issue printing text that is wrapped. It is NOT a merged cell;
the column has a fixed width; the cell has wrap text turned on and it is set
to auto fit. (This is important as I have a workbook with over 150
worksheets; each worksheet has 27 rows with text in this column and each row
in each sheet is different; some have several sentences and some have two
words. So I cannot take the time to manually adjust these rows.)

When I look at the text on my screen it shows up perfectly. When I print on
one printer it comes out fine; on another printer it cuts off the bottom line
on some rows and cuts into the bottom line on many others. I assume this is
some kind of printer problem. Is there a way I can fix it for ANY printer? I
am going to have to send this workbook to someone else when it is finished
and I have no idea what that person's printer will do.

Any help is much appreciated. Thanks!

Stacy

 

[Dave Peterson]

Maybe you could add an alt-enter as the last character in the cell.

You'll see an "extra" line on some printers, but at least the last line being
chopped won't matter.

 

[stacyjean622]

Dave,

I thought about doing that, but I don't know how to "automatically" add
alt-enter to all of these cells. As I mentioned, I will have a total of over
3,000 cells like this - all with different text. I definitely don't want to
have to manually add the alt-enter. I wasn't sure how to write a VBA code for
it or if there was a simpler way to add alt-enter to all of the cells.

Stacy

 

[Dave Peterson]

You'll have to adjust the range that contains those long strings. I used
A1:A9999.

Option Explicit
Sub testme02()

Dim myRng As Range
Dim myCell As Range
Dim myAddr As String
Dim wks As Worksheet
Dim iCtr As Long

Dim CalcMode As Long
Dim ViewMode As Long

Application.ScreenUpdating = False

CalcMode = Application.Calculation
Application.Calculation = xlCalculationManual

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

'what range contains those strings?
myAddr = "A1:A9999"

iCtr = 0
For Each wks In ActiveWorkbook.Worksheets
Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(wks.Range(myAddr), _
wks.Range(myAddr).Cells _
.SpecialCells(xlCellTypeConstants, xlTextValues))
On Error GoTo 0

If myRng Is Nothing Then
'do nothing on this sheet
Else
For Each myCell In myRng.Cells
iCtr = iCtr + 1
If iCtr Mod 50 = 0 Then
Application.StatusBar = "Processing: " _
& myCell.Address(external:=True)
End If

If Trim(myCell.Value) = "" Then
'skip it
Else
If Right(myCell.Value, 1) = vbLf Then
'already has one, so skip it
Else
myCell.Value = myCell.Value & vbLf
End If
End If
Next myCell
End If
Next wks

'put things back to what they were
Application.Calculation = CalcMode
ActiveWindow.View = ViewMode
Application.ScreenUpdating = True
Application.StatusBar = False

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[stacyjean622]

Thanks Dave - that sub worked perfectly. (And it was definitely beyond my
prowess as a beginner with macros, so I really appreciate the help!)

Stacy