Moving HPageBreaks if Splitting Rows with Data

[sdbroyles]

Hello,

I have looked in the forum and googled for code to do what I need with no luck. Hoping for some help. If a hpagebreak falls between 2 rows that contain data anywhere in the rows, move the hpagebreak up 1 line, if the hpagebreak still falls between 2 rows that contain data, move up another row, etc. If the hpagebreak falls between 2 rows not containing data, or 2 rows where only 1 row contains data and the other does not, then do nothing and move on to the next hpagebreak. The first snippit is an example of the hpagebreak not needing to be moved. The 2nd snippit is an example of the hpagebreak needing to be moved up 1 line. The 3rd snippit is an example of the hpagebreak nedding to be moved up 3 lines. Any help would be greatly appreciated.

 

[sdbroyles]

This worked perfectly!!! Thank you so much for your help Domenic. Looking at your code, it's no wonder I couldn't figure it out, I'm no where near your level of expertise. After adding your code to my macro, it highlighted an issue my macro appaerntly had with setting the print area. Would you be able to tell me why this code is not picking up the last row with data in it? I know it has something to do with the Range because when I changed the Range to ("J2500") it picked up the last row. So, I guess I need to specify a range of columns and row number but haven't had any success yet.

ActiveSheet.PageSetup.PrintArea = "$A$a:$J$" & Range ("A2500").End(xlUp).Row

View attachment 56385

ActiveSheet.PageSetup.PrintArea = "$A$1:$J$" & Range ("A2500").End(xlUp).Row

There was a typo in the code above.

 

[Domenic]

That's great, I'm glad I was able to help. It always helps to have sample data, along with expected results.

Try finding the last used row using the following function . . .

Public Function GetLastRow(Optional ByVal ws As Worksheet = Nothing)

    If ws Is Nothing Then
        Set ws = ActiveSheet
    End If
    
    On Error Resume Next
    With ws
        GetLastRow = .Cells.Find(What:="*", _
                                    after:=.Range("A1"), _
                                    Lookat:=xlPart, _
                                    LookIn:=xlFormulas, _
                                    SearchOrder:=xlByRows, _
                                    SearchDirection:=xlPrevious, _
                                    MatchCase:=False).Row
    End With
    On Error GoTo 0
    
End Function

Then, to find the last row in the active worksheet and set the print area, try . . .

    Dim lastRow As Long
    lastRow = GetLastRow(ActiveSheet)

    ActiveSheet.PageSetup.PrintArea = "$A$1:$J$" & lastRow

Note that you can also get the last row of the active worksheet like this . . .

lastRow = GetLastRow()

Or, you can use a specific worksheet . . .

lastRow = GetLastRow(Worksheets("Sheet2"))

and

lastRow = GetLastRow(Workbooks("Book2.xlsx").Worksheets("Sheet1"))

Hope this helps!