Setting Page Breaks

[Dave]

This is driving me crazy...

I have a spreadsheet with 3 rows merged into 1 (many of these).
Sometimes when I insert a new row (3 excel rows) the page break will
be inserted in the middle of my row. How can I, with a button, set the
page break every 27 rows per page and print preview? And incidentally,
to the left of column N?

Thanks,

Dave

 

[GS]

This is driving me crazy...

I have a spreadsheet with 3 rows merged into 1 (many of these).
Sometimes when I insert a new row (3 excel rows) the page break will
be inserted in the middle of my row. How can I, with a button, set the
page break every 27 rows per page and print preview? And incidentally,
to the left of column N?

Thanks,

Dave

Did you try using Page Break Preview from the Print Preview window? It
will allow you to drag-n-drop default page breaks, OR you can manually
'Insert' pagebreaks at the active cell location from the Insert menu.

 

[Dave]

Did you try using Page Break Preview from the Print Preview window? It
will allow you to drag-n-drop default page breaks, OR you can manually
'Insert' pagebreaks at the active cell location from the Insert menu.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

I have no problems setting page breaks manually, but many of the other
users will. So I'm looking for vba code that I can attach to a button
to ensure that page breaks are where they need to be. The users will
click this button to do the printing.

Thanks for the quick response.

Dave

 

[Jim Cone]

Sub BreadcrumbWaggles()
'Jim Cone - Portland Oregon - Sept 09, 2011
Dim N As Long
Dim LastRow As Long
Const lngInterval As Long = 27
Const lngColumn As Long = 14
LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row

Application.ScreenUpdating = False
ActiveSheet.Cells.PageBreak = xlNone
For N = lngInterval + 1 To LastRow Step lngInterval
ActiveSheet.Range(ActiveSheet.Cells(N, 1), _
ActiveSheet.Cells(N, lngColumn)).PageBreak = xlPageBreakManual
Next 'N
ActiveWindow.View = xlPageBreakPreview
Application.ScreenUpdating = True
End Sub
'---
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(Special Print XL add-in: long columns printed side by side)




"Dave" <[email protected]>
wrote in message

Did you try using Page Break Preview from the Print Preview window? It
will allow you to drag-n-drop default page breaks, OR you can manually
'Insert' pagebreaks at the active cell location from the Insert menu.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

I have no problems setting page breaks manually, but many of the other
users will. So I'm looking for vba code that I can attach to a button
to ensure that page breaks are where they need to be. The users will
click this button to do the printing.

Thanks for the quick response.

Dave

 

[GS]

Dave explained on 9/9/2011 :

I have no problems setting page breaks manually, but many of the other
users will. So I'm looking for vba code that I can attach to a button
to ensure that page breaks are where they need to be. The users will
click this button to do the printing.

Thanks for the quick response.

Dave

You might find this does a quicker job...

Sub SetPageBreaks()
Dim lRow&, lLastRow&, r& '//as Long
Const lPgBreak& = 10 '//as Long
lRow = 10
lLastRow = Find_LastRowOrCol
Application.ScreenUpdating = False
ActiveSheet.ResetAllPageBreaks
With ActiveWindow.SelectedSheets.HPageBreaks
For r = 1 To CInt(lLastRow / lPgBreak)
.Add Before:=Cells(lRow + 1, 1): lRow = lRow + lPgBreak
Next 'lRow
End With 'ActiveWindow.PageBreaks
Application.ScreenUpdating = True
End Sub


'Reusable helper function
Public Function Find_LastRowOrCol(Optional Wks As Worksheet, _
Optional Col As Boolean) As Long
' Finds the last row or column containing data
If Wks Is Nothing Then Set Wks = ActiveSheet
With Wks
If Col Then
Find_LastRowOrCol = .Cells.Find(What:="*", _
After:=Cells(Rows.Count, Columns.Count), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Column
Else
Find_LastRowOrCol = .Cells.Find(What:="*", _
After:=Cells(Rows.Count, Columns.Count), _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Row
End If
End With 'Wks
End Function

 

[Dave]

Sub BreadcrumbWaggles()
'Jim Cone - Portland Oregon - Sept 09, 2011
 Dim N As Long
 Dim LastRow As Long
 Const lngInterval As Long = 27
 Const lngColumn As Long = 14
 LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row

 Application.ScreenUpdating = False
 ActiveSheet.Cells.PageBreak = xlNone
 For N = lngInterval + 1 To LastRow Step lngInterval
     ActiveSheet.Range(ActiveSheet.Cells(N, 1), _
     ActiveSheet.Cells(N, lngColumn)).PageBreak = xlPageBreakManual
 Next 'N
 ActiveWindow.View = xlPageBreakPreview
 Application.ScreenUpdating = True
End Sub
'---
Jim Cone
Portland, Oregon USA  .http://www.mediafire.com/PrimitiveSoftware .
(Special Print XL add-in:  long columns printed side by side)

"Dave" <[email protected]>
wrote in message


I have no problems setting page breaks manually, but many of the other
users will. So I'm looking for vba code that I can attach to a button
to ensure that page breaks are where they need to be. The users will
click this button to do the printing.

Thanks for the quick response.

Dave- Hide quoted text -

- Show quoted text -

Thanks Jim, that works great for the first page. The second page
however ends up with the dotted line in the middle of one of my merge
rows.

Dave

 

[GS]

Dave used his keyboard to write :

Thanks Jim, that works great for the first page. The second page
however ends up with the dotted line in the middle of one of my merge
rows.

Dave

The problem you have is associated with top/bottom margin settings AND
that you use merged rows instead of multi-line single cells. Fix those
issues first and the page breaks with work as expected.

 

[GS]

Minor revision...

Public Function Find_LastRowOrCol(Optional Wks As Worksheet, _
Optional Col As Boolean) As Long
' Finds the last row or column containing data
If Wks Is Nothing Then Set Wks = ActiveSheet
With Wks
If Col Then
Find_LastRowOrCol = .Cells.Find(What:="*", _
After:=Cells(Rows.Count, Columns.Count), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Column
Else
Find_LastRowOrCol = .Cells.Find(What:="*", _
After:=Cells(Rows.Count, Columns.Count), _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Row
End If
End With 'Wks Set Wks = Nothing
End Function

Not a big deal on a one by one case, but I don't like to leave anything
up to VB[A] to do by implicity because that ALWAYS requires extra
processing on VB[A]'s part. Where it will impact performance
(cummulatively) is when the function is used to collect last row/col
positions for all sheets in a multi-sheet wkb OR multiple sheets
accross multiple open wkbs.

 

[Dave]

Dave explained on 9/9/2011 :









You might find this does a quicker job...

  Sub SetPageBreaks()
    Dim lRow&, lLastRow&, r& '//as Long
    Const lPgBreak& = 10 '//as Long
    lRow = 10
    lLastRow = Find_LastRowOrCol
    Application.ScreenUpdating = False
    ActiveSheet.ResetAllPageBreaks
    With ActiveWindow.SelectedSheets.HPageBreaks
      For r = 1 To CInt(lLastRow / lPgBreak)
        .Add Before:=Cells(lRow + 1, 1): lRow = lRow + lPgBreak
      Next 'lRow
    End With 'ActiveWindow.PageBreaks
    Application.ScreenUpdating = True
  End Sub

'Reusable helper function
  Public Function Find_LastRowOrCol(Optional Wks As Worksheet, _
                                    Optional Col As Boolean) As Long
  ' Finds the last row or column containing data
    If Wks Is Nothing Then Set Wks = ActiveSheet
    With Wks
      If Col Then
        Find_LastRowOrCol = .Cells.Find(What:="*", _
                After:=Cells(Rows.Count, Columns.Count), _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious).Column
      Else
        Find_LastRowOrCol = .Cells.Find(What:="*", _
                After:=Cells(Rows.Count, Columns.Count), _
                SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious).Row
      End If
    End With 'Wks
  End Function

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

I must be missing something. all this does is reset the page breaks.
My head hurts.

Thanks anyway,

Dave

 

[Jim Cone]

It was your request to create a page break every 27 rows.
You can try different spacing for the page breaks by changing the number in this code line...

"Const lngInterval As Long = 27"

'---
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(Special Print XL add-in: long columns printed side by side)





"Dave" <[email protected]>
wrote in message
Thanks Jim, that works great for the first page. The second page
however ends up with the dotted line in the middle of one of my merge rows.

Dave

 

[GS]

Dave formulated on Friday :

must be missing something. all this does is reset the page breaks.
My head hurts.

That's what you asked for. Fix your sheet so the page breaks work
properly! OR, as Jim suggests, adjust your lines per page so it works
properly!

Otherwise, both code samples do EXACTLY what you asked for!