View Full Version : auto set page break to last row with data
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
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?
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.