Hide 2nd pagebreak VBA

[bluesteelday]

Hi Guys,
I have page breaks in every excel sheet (Page 1,Page 2) set for printing purposes.

What I would like to do is hide the 2nd page in each sheet and keep the 1st page.
I need this to be active for all sheets except sheet named "Other"
This is my code:


For Each ws in application.activeworkbook.worksheets
if ws.name <> ("Other") Then:

-----------------------
Thanks!

 

[pbornemeier]

Assuming your data is such that the second page is below the first, not to the right of the first:

Option Explicit

Sub HideRowsAfterFirstSheet()

    Dim ws As Worksheet
    Dim lLastRow As Long
    Dim HBreak1 As String
    Dim lHBreak1Row As Long
    
    For Each ws In Application.ActiveWorkbook.Worksheets
        If ws.Name <> ("Other") Then
            With ws
                lLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
                If .HPageBreaks.Count > 0 Then
                    HBreak1 = .HPageBreaks(1).Location
                    lHBreak1Row = .Range(HBreak1).Row
                    .Range(.Cells(lHBreak1Row, 1), .Cells(lLastRow, 1)).EntireRow.Hidden = True
                End If
            End With
        End If
    Next
    
End Sub