Hide Page break 2

Hi Guys,
I have page breaks in every excel sheet (Page 1,Page 2) set for printing purposes.
Any way to hide all contents in everysheet starting with the 2nd page(meaning to display just the 1st page break) in every sheet?

I need this to be active for all sheets except Other
This is my code:


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

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

Hello.

See if it helps.

Sub HID_Sheets()

Dim w As Worksheet
Dim wp As Worksheet

Set wp = sheets("Other")


    For Each w In thisworkbook.Worksheets
    
        If w.Name <> wp.Name Then
        
        w.Visible = xlSheetHidden
        
        End If
    
    
    Next w

End Sub

Hi ,
Thanks for the reply!
The issue is that I have 2 pages(in each sheet) so what your code will do is hide all sheets other than "other"

What I would like to do is hide the 2nd page in each sheet and keep the 1st page.
The reason I want to do it is because in some sheets the 2nd sheet does not contain any data so I wouldn't like to view it at all.
Thanks

Hi Vadimchik,

Great question. For your specific question, the solution is relatively simple, but for the general case a little more complicated. Issues for the general case include:
a. Blank Sheet (no Print Area)
b. Sheet using the default Print Area (Print Area never defined by the User)
c. Sheet where Page 1 is not in Cell 'A1'.
d. Non-standard Print Area (not addressed here)

See the attached file that contains the following code in ordinary code module ModDisplayHidePages2ThruN:

Option Explicit

Sub DisplayAllSheetsInNormalView()

  Dim wks As Worksheet
  Dim myOriginalActiveSheet As Worksheet
  
  'Disable Excel Events
  'Disable Screen Updating (to improve speed performance and to eliminate screen flicker)
  Application.EnableEvents = False
  Application.ScreenUpdating = False
  
  'Get the Object for the 'Active Sheet'
  Set myOriginalActiveSheet = ActiveSheet
  
  'Process each Worksheet in the Workbook
  For Each wks In Application.ActiveWorkbook.Worksheets
  
    wks.Activate
    ActiveWindow.View = xlNormalView

  Next wks
   
  'Put the focus on the Original Sheet
  myOriginalActiveSheet.Select
  
  'Clear object pointers
  Set myOriginalActiveSheet = Nothing

  'Enable Excel Events and Screen Updating
  Application.EnableEvents = True
  Application.ScreenUpdating = True

  'Display a completion message
  MsgBox "DisplayAllSheetsInNormalView() completed."
  
End Sub

Sub DisplayAllSheetsInPageBreakView()

  Dim wks As Worksheet
  Dim myOriginalActiveSheet As Worksheet
  
  'Disable Excel Events
  'Disable Screen Updating (to improve speed performance and to eliminate screen flicker)
  Application.EnableEvents = False
  Application.ScreenUpdating = False
  
  'Get the Object for the 'Active Sheet'
  Set myOriginalActiveSheet = ActiveSheet
  
  'Process each Worksheet in the Workbook
  For Each wks In Application.ActiveWorkbook.Worksheets
  
    wks.Activate
    ActiveWindow.View = xlPageBreakPreview

  Next wks
   
  'Put the focus on the Original Sheet
  myOriginalActiveSheet.Select
  
  'Clear object pointers
  Set myOriginalActiveSheet = Nothing

  'Enable Excel Events and Screen Updating
  Application.EnableEvents = True
  Application.ScreenUpdating = True

  'Display a completion message
  MsgBox "DisplayAllSheetsInPageBreakView() completed."
  
End Sub


Sub HidePages2thruNOnEachSheet()
  'This hides pages '2 thru n' on all Sheets except Sheet 'Other'

  Dim wks As Worksheet
  Dim myOriginalActiveSheet As Worksheet
  
  Dim iFirstColumnPrinted As Long
  Dim iFirstColumnToHide As Long
  Dim iFirstRowPrinted As Long
  Dim iFirstRowToHide As Long
  Dim iHorizontalPageBreakCount As Long
  Dim iLastColumnPrinted As Long
  Dim iLastColumnUsed As Long
  Dim iLastRowPrinted As Long
  Dim iLastRowToHide As Long
  Dim iLastRowUsed As Long
  Dim iPageBreakColumn As Long   'The 'Page Break' is in the Column before this Column
  Dim iPageBreakRow As Long      'The 'Page Break' is on the Row before this Row
  Dim iVerticalPageBreakCount As Long
  
  Dim sFirstColumnToHide As String
  Dim sLastColumnToHide As String
  Dim sPrintArea As String
  Dim sRange As String
  Dim sSheetName As String
  Dim sVeryLastColumn As String

  'Disable Excel Events
  'Disable Screen Updating (to improve speed performance and to eliminate screen flicker)
  Application.EnableEvents = False
  Application.ScreenUpdating = False
  
  'Get the Object for the 'Active Sheet'
  Set myOriginalActiveSheet = ActiveSheet
  
  sVeryLastColumn = LjmExcelColumnNumberToChar(Columns.Count)
  
  For Each wks In Application.ActiveWorkbook.Worksheets
    sSheetName = wks.Name
  
    If UCase(sSheetName) <> ("OTHER") Then
    
      'Get the 'Last Row Used' and the 'Last Column Used' on the Sheet
      On Error Resume Next
      iLastRowUsed = 0
      iLastColumnUsed = 0
      iLastRowUsed = wks.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
      iLastColumnUsed = wks.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
      On Error GoTo 0
  
      'Process the Sheet, only if the Sheet is NOT EMPTY
      'NOTE: If the 'Last Row Used' exists, then the 'Last Column Used' MUST also exist
      If iLastRowUsed > 0 Then
  
  
        'Initialize the 'Page Break' Row and Column
        iPageBreakRow = 0
        iPageBreakColumn = 0
    
        'Get the Print Area for the Sheet
        'Get the 'First' and 'Last' Rows and Columns
        sPrintArea = wks.PageSetup.PrintArea
        If Len(sPrintArea) = 0 Then
          'Default Print Area Used - Never Specified by User
          iFirstRowPrinted = 1
          iFirstColumnPrinted = 1
          iLastRowPrinted = Rows.Count
          iLastColumnPrinted = Columns.Count
        Else
          'User Specified Print Area
          iFirstRowPrinted = wks.Range(sPrintArea).Row
          iFirstColumnPrinted = wks.Range(sPrintArea).Column
          iLastRowPrinted = iFirstRowPrinted + wks.Range(sPrintArea).Rows.Count - 1
          iLastColumnPrinted = iFirstColumnPrinted + wks.Range(sPrintArea).Columns.Count - 1
        End If
      
        'Get the Horizontal 'Page Break' Count
        'If there are 'Page Breaks', get the 'Page Break' Row
        iHorizontalPageBreakCount = wks.HPageBreaks.Count
        If iHorizontalPageBreakCount > 0 Then
          iPageBreakRow = wks.HPageBreaks(1).Location.Row
        End If
      
        'Get the Vertical 'Page Break' Count
        'If there are 'Page Breaks', get the 'Page Break' Column
        iVerticalPageBreakCount = wks.VPageBreaks.Count
        If iVerticalPageBreakCount > 0 Then
          iPageBreakColumn = wks.VPageBreaks(1).Location.Column
        End If
    
        'Debug.Print sSheetName, iFirstRowPrinted, iFirstColumnPrinted, iPageBreakRow, iPageBreakColumn
        
        'If there are NO Horizontal 'Page Breaks' use the 'Last Row Of the 'Print Area'
        'If there is NO 'Last Row of the Print Area' (overflow), use the 'Last Row Used
        If iPageBreakRow = 0 Then
          iPageBreakRow = iLastRowPrinted + 1
        
          If iPageBreakRow > Rows.Count Then
            iPageBreakRow = iLastRowUsed + 1
          End If
        
        End If
        
        'If there are NO Vertical 'Page Breaks' use the 'Last Column Of the 'Print Area'
        'If there is NO 'Last Column of the Print Area' (overflow), use the 'Last Column Used
        If iPageBreakColumn = 0 Then
          iPageBreakColumn = iLastColumnPrinted + 1
        
          If iPageBreakColumn > Columns.Count Then
            iPageBreakColumn = iLastColumnUsed + 1
          End If
        
        End If
    
        'Hide Rows ABOVE the 'First Print Page' if the 'First Page' is offset
        If iFirstRowPrinted > 1 Then
          iFirstRowToHide = iFirstRowPrinted - 1
          sRange = "1:" & iFirstRowToHide
          wks.Rows(sRange).EntireRow.Hidden = True
        End If
    
        'Hide Columns LEFT of the 'First Print Page' if the 'First Page' is offset
        If iFirstColumnPrinted > 1 Then
          iFirstColumnToHide = iFirstColumnPrinted - 1
          sFirstColumnToHide = LjmExcelColumnNumberToChar(iFirstColumnToHide)
          sRange = "A:" & sFirstColumnToHide
          wks.Columns(sRange).EntireColumn.Hidden = True
        End If
    
        'Hide Rows BELOW the 'First Page' (unless the 'First Page' uses the 'Last Row')
        If iPageBreakRow < Rows.Count Then
          sRange = iPageBreakRow & ":" & Rows.Count
          wks.Rows(sRange).EntireRow.Hidden = True
        End If
    
        'Hide Columns RIGHT the 'First Page' (unless the 'First Page' uses the 'Last Column')
        If iPageBreakColumn < Columns.Count Then
          sLastColumnToHide = LjmExcelColumnNumberToChar(iPageBreakColumn)
          sRange = sLastColumnToHide & ":" & sVeryLastColumn
          wks.Columns(sRange).EntireColumn.Hidden = True
        End If

      End If
    
    End If
   
   Next wks

  'Put the focus on the Original Sheet
  myOriginalActiveSheet.Select
  
  'Clear object pointers
  Set myOriginalActiveSheet = Nothing

  'Enable Excel Events and Screen Updating
  Application.EnableEvents = True
  Application.ScreenUpdating = True

  'Display a completion message
  MsgBox "HidePages2thruNOnEachSheet() completed."
  
End Sub

Sub DisplayAllRowAndColumnsOnEachSheet()
  'This displays all Rows and Columns on Each Sheet (Sheet 'Other' is ignored)
  
  Dim wks As Worksheet
  Dim myOriginalActiveSheet As Worksheet
  Dim sSheetName As String
  Dim sLastColumn As String
  
  'Disable Excel Events
  'Disable Screen Updating (to improve speed performance and to eliminate screen flicker)
  Application.EnableEvents = False
  Application.ScreenUpdating = False
  
  'Get the Object for the 'Active Sheet'
  Set myOriginalActiveSheet = ActiveSheet
  
  'Get the Last Column as a String
  sLastColumn = LjmExcelColumnNumberToChar(Columns.Count)
  
  'Process each Worksheet in the Workbook except Sheet 'Other'
  For Each wks In Application.ActiveWorkbook.Worksheets
  
    'Get the 'Sheet Name'
    sSheetName = wks.Name
  
    'Process the Sheet unless it is Sheet 'Other'
    If UCase(sSheetName) <> ("OTHER") Then
    
      'Display All Rows
      'Display All Columns
      'Put the focus on Cell 'A1'
      wks.Rows("1:" & Rows.Count).EntireRow.Hidden = False
      wks.Columns("A:" & sLastColumn).EntireColumn.Hidden = False
      wks.Select
      wks.Range("A1").Select
    End If

   Next wks
   
  'Put the focus on the Original Sheet
  myOriginalActiveSheet.Select
  
  'Clear object pointers
  Set myOriginalActiveSheet = Nothing

  'Enable Excel Events and Screen Updating
  Application.EnableEvents = True
  Application.ScreenUpdating = True

  'Display a completion message
  MsgBox "DisplayAllRowAndColumnsOnEachSheet() completed."
  
End Sub



Function LjmExcelColumnNumberToChar(InputColumn As Long) As String
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
' This recursive function converts an Excel integer column number to "character column letter(s)"
' e.g. convert     1 to "A"
' e.g. convert     28 to "AB"
' e.g. convert    256 to "IV"
' e.g. convert    676 to "YZ"
' e.g. convert    702 to "ZZ"
' e.g. convert 16,384 to "XFD"
'
' Reference: http://stackoverflow.com/questions/12796973/function-to-convert-column-number-to-letter
' Thank you Nikolay Ivanov
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  
    Dim iAlpha As Long
    Dim iRemainder As Long

    If InputColumn <= 26 Then
        LjmExcelColumnNumberToChar = Chr(InputColumn + 64)
    Else
        iRemainder = InputColumn Mod 26
        iAlpha = Int(InputColumn / 26)
        If iRemainder = 0 Then
            iRemainder = 26
            iAlpha = iAlpha - 1
        End If
        LjmExcelColumnNumberToChar = LjmExcelColumnNumberToChar(iAlpha) & Chr(iRemainder + 64)
    End If

End Function

Lewis

Wow, thanks Lewis. Looks great, although more code than I anticipated. Won't that affect runtime when having 49 sheets ?

It was relatively quick with the six small sheets. Runtime with 49 sheets should be 5 seconds or less.

Thanks Lewish,
Really helpful and beautifully explained!

This could be qualifies as one of the simple solutions, as Lewis alluded to.

Sub Hide_From_2nd_Page_On()
Dim i As Long, lr As Long, fr As Long, ws As Worksheet
    For i = 1 To ActiveWorkbook.Sheets.Count
    Set ws = Sheets(i)
        If ws.Name <> "Other" Then
            lr = ws.Cells.Find("*", , , , xlByRows, xlPrevious).Row
                fr = ws.HPageBreaks(1).Location.Row
            ws.Range(ws.Cells(fr, 1), ws.Cells(lr, 1)).EntireRow.Hidden = True
        End If
    Next i
End Sub

Note:No error checking in the code.

https://www.ozgrid.com/forum/forum/h...page-excel-vba