Thread: Hide rows before printing

Hello,

This code was working and now is not. We have a mixed environment of Excel 2003 and Excel 2010. It was working on both until recently.
The MSG box pops up normally, so I know that the code is being parsed, but the Hiding and Un-hiding of rows is not.

Private Sub Workbook_Open()

MsgBox "The information in this workbook is privileged, and strictly confidential it is intended solely for the use of Wellington Security Systems. If the reader of this message is not an employee or agent of Wellington Security Systems, dissemination, distribution, copying or other use of the information contained in this workbook is strictly prohibited. If you have received this workbook and you were not the original intended recipient, please first notify the sender immediately and then delete this workbook from all data storage devices and destroy all hard copies.", vbExclamation, "Confidential Information"

End Sub
Private Sub Workbook_BeforePrint(Cancel As Boolean)
     
     Dim Firstrow As Long
     Dim Lastrow As Long
     
     With ActiveSheet
      
      Firstrow = .UsedRange.Cells(1).Row
      Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

     End With
     
  If LCase(ActiveSheet.Name) = "burg - fire - access" Then
    
   Cancel = True
  
   Application.EnableEvents = False
   Application.ScreenUpdating = False
  
    With ActiveSheet
     
     Rows.EntireRow.Hidden = False
     
     For RowCnt = Firstrow To Lastrow
        If Cells(RowCnt, 1).Value <> "x" Then
           Cells(RowCnt, 1).EntireRow.Hidden = True
        End If
     Next RowCnt
    
    .ResetAllPageBreaks
    .PageSetup.Zoom = 80
     
    .PrintOut
    
    Rows.EntireRow.Hidden = False
    
  End With
 
  Application.EnableEvents = True
  Application.ScreenUpdating = True
  
 ElseIf LCase(ActiveSheet.Name) = "rsi" Then
    
   Cancel = True
  
   Application.EnableEvents = False
   Application.ScreenUpdating = False
  
    With ActiveSheet
     
     Rows.EntireRow.Hidden = False
     
     For RowCnt = Firstrow To Lastrow
        If Cells(RowCnt, 1).Value <> "x" Then
           Cells(RowCnt, 1).EntireRow.Hidden = True
        End If
     Next RowCnt
    
    .ResetAllPageBreaks
    .PageSetup.Zoom = 80
     
    .PrintOut
    
    Rows.EntireRow.Hidden = False
    
  End With
 
  Application.EnableEvents = True
  Application.ScreenUpdating = True
   
 ElseIf LCase(ActiveSheet.Name) = "cctv" Then
     
   Cancel = True
  
   Application.EnableEvents = False
   Application.ScreenUpdating = False
  
    With ActiveSheet
    
     Rows.EntireRow.Hidden = False
     
     For RowCnt = Firstrow To Lastrow
        If Cells(RowCnt, 1).Value <> "x" Then
           Cells(RowCnt, 1).EntireRow.Hidden = True
        End If
     Next RowCnt
    
    .ResetAllPageBreaks
    .PageSetup.Zoom = 80
     
    .PrintOut
    
    Rows.EntireRow.Hidden = False
    
  End With
 
  Application.EnableEvents = True
  Application.ScreenUpdating = True
  
 ElseIf LCase(ActiveSheet.Name) = "aes intellinet" Then
     
   Cancel = True
  
   Application.EnableEvents = False
   Application.ScreenUpdating = False
  
    With ActiveSheet
     
     Rows.EntireRow.Hidden = False
     
     For RowCnt = Firstrow To Lastrow
        If Cells(RowCnt, 1).Value <> "x" Then
           Cells(RowCnt, 1).EntireRow.Hidden = True
        End If
     Next RowCnt
    
    .ResetAllPageBreaks
    .PageSetup.Zoom = 80
     
    .PrintOut
    
    Rows.EntireRow.Hidden = False
    
  End With
 
  Application.EnableEvents = True
  Application.ScreenUpdating = True
   
 Else
  
   Cancel = False
    
 End If
  
 
End Sub

Briefly, this is a sales workup with parts numbers and quantities, this allows all of the parts to be listed, and only print a page with parts that have a quantity greater than 0. There is a simple formula in column A which places an X in rows which have a number greater than 0 in the quantity cell.

What's happening now is simply that the entire worksheet is printing, it is not hiding the rows as it used to.

Thanks in advance for looking.

It works for me.
You need to step through the code on your machine, this allows you to validate each line of code in turn: There are a couple of minor issues which may cause problems - so we'll also use some neatened up code.
Select your burg - fire - access sheet
Open the VB editor - Alt+F11
Replace your macro with this (leave the open workbook event):

Private Sub Workbook_BeforePrint(Cancel As Boolean)
End Sub
Sub test()

Dim rCell As Range

With ActiveSheet
    Select Case LCase(.Name)
    Case "burg - fire - access", "rsi", "cctv", "aes intellinet"
    
        Cancel = True
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        
        .Cells.EntireRow.Hidden = False
        For Each rCell In .UsedRange.Columns(1).Cells
            If rCell <> "x" Then rCell.EntireRow.Hidden = True
        Next rCell
        
        .ResetAllPageBreaks
        .PageSetup.Zoom = 80
        '.PrintOut
        
        .Cells.EntireRow.Hidden = False
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End Select
End With

End Sub

Make sure the cursor is below the 'Sub test()' line.
Press F8 - this starts running the macro in break mode (i.e. it waits for you to continue).
Arrange windows so you can see both the VBE and the Excel workbook - so you can watch it manipulate the rows (or not...)
Press F8 repeatedly, watching the effect of each line - the first thing to look for is when you get to this line:

        .Cells.EntireRow.Hidden = False

Tell us how it goes...