Automatic line break instead of moving to next page

[Orongo]

Hi, I am writing a manual in Excel using the page layout view. Some of my instructions are long so the text goes across pages. Instead of using wrapping and merging cells when this happens, is there a setting I can use in Excel instead?

 

[Anthony47]

My first suggestion: next time use a word processor that has specific text grouping options

If it's too late to switch to Word in the current work, then let'u try a trick
a) use column A to mark with a 1 the lines that need to stay together and leave blank all the other cells in the column; let's assume that you will group a fraction of the lines than are normally included in one of your printed pages.
b) set the Print Area excluding column A
c) use this code to reset all the horizontal page breaks to avoid conflicts with the marked lines:

Sub KeepTogether()
Dim HPB, CHbr As Long, I As Long, J As Long
Dim HBPrePost(), reFor As Boolean
'
Call ResetHB                        'Remove
Application.ScreenUpdating = False
ActiveWindow.View = xlPageBreakPreview
reLoop:
For J = 1 To ActiveSheet.HPageBreaks.Count
    CHbr = ActiveSheet.HPageBreaks(J).Location.Row
    I = 0
    If Cells(CHbr, "A") <> "" Then
        If Cells(CHbr - 1, "A") <> "" Then
            For I = CHbr - 1 To 1 Step -1
                If Cells(I, 1) = "" Then Exit For
            Next I
        End If
        If I > 1 And I < (CHbr - 1) Then
                ActiveWindow.ActiveSheet.HPageBreaks.Add Before:=Rows(I + 1)
                Cells(I + 1, 1).Value = 10
                reFor = True
        End If
    End If
    If reFor Then Exit For
Next J
If reFor Then reFor = False: GoTo reLoop
Application.ScreenUpdating = True
ActiveWindow.View = xlNormalView
MsgBox ("Grouping completed")
End Sub

Sub ResetHB()
ActiveWindow.View = xlPageBreakPreview
ActiveWindow.View = xlNormalView
For J = ActiveSheet.HPageBreaks.Count To 1 Step -1
    CHbr = ActiveSheet.HPageBreaks(J).Location.Row
    If Cells(CHbr, 1).Value = 10 Then
        ActiveSheet.HPageBreaks(J).Delete
'        ActiveSheet.HPageBreaks(3).Delete
        Cells(CHbr, 1).Value = 1
    End If
Next J
End Sub

The code contains two macros:
1) Sub KeepTogether; this is the macro that you have to run
2) Sub ResetHB; this removes the pagebreaks that have been already inserted by Sub KeepTogether. It is "called" by the Sub KeepTogether, but can be even executed to remove the previously inserted page breaks without reapplying new ones

Sub ResetHB also works exploiting the content of column A. Therefore it do not delete page breaks that had been manually added to your workbook
In case you wish removing all inserted pagebreaks (either inserted manually or by the macro KeepTogether above) you can run the following macro:

Sub RemoveAddedPageBreaks()
    Cells.Select
    ActiveSheet.ResetAllPageBreaks
    Range("A1").Select
End Sub

Final remark: test what I proposed on a copy of your workbook

Hope I didn't confuse you beyond my wishes...

Bye

 

[Orongo]

Wow thank you for a complete answer. I worry because the workbook will be shared by many and over time, macros are not possible becuase of user limitations. Excel show itself yet again it flaws only to be solved by macros and behind-the-curtain-solutions ?

 

[Anthony47]

Hummm... A son with many fathers???

The macro have to be used by the person who write the instructions; he prepare the "manual" and transform it to a readable document (a pdf, a booklet, ???)

And I agree, Excel is a poor word processor...

Bye