PDA

View Full Version : auto set page break to last row with data



wilg
04-02-2011, 09:08 AM
I have a sheet with data from columns a to v and row 2 to variable end.

If on a specific sheet ("3 Summary") I would like to automatically set the horizontal bage break to the last row which could be row 200 to 300.

If been trying to use many codes I've come across but it does not seem to work. Any help is appreciated.

mdmackillop
04-02-2011, 02:47 PM
I don't know if you want the break before or after the last cell: this is for before
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim c As Range
If Sh.Name = "3 Summary" Then
Set c = Range("A:V").Find("*", searchorder:=xlRows, searchdirection:=xlPrevious)
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=c 'or Before:=c.Offset(1)
End If
End Sub

wilg
05-02-2011, 06:59 PM
Hi mdmackillop..

Sorry for delayed reply, but this does not seem to work for me. I get no movement of the pagebreaks what so ever.

mdmackillop
05-03-2011, 01:02 PM
Can you post a sample workbook and indicate where the page breaks should be?

wilg
05-03-2011, 07:27 PM
It would be difficult to post the workbook. I tested your code on a blank workbook and it does work.

With my workbook I have formulas in column a and column b. column a if iserror give value ""

Would that be a factor?

mdmackillop
05-04-2011, 12:10 AM
I cannot see that it would.