Printing Visible Data Only

[hmltnangel]

Almost got it but just cant see where I have went wrong. Any help would be appreciated

Need to print only the rows where there is data. Excel seems to like printing all rows though

Sub Printsheet()
    
    ActiveSheet.Unprotect
    
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    
    Rows("2:22").Select
    Selection.EntireRow.Hidden = False
    
    Dim LR As Long
    LR = Range("B" & Rows.Count).End(xlUp).Row
    
    With ActiveSheet
    .PageSetup.BlackAndWhite = False
    .PrintOut Copies:=1, Collate:=True
    .PrintArea = Range("B1:G606" & LR).SpecialCells(xlCellTypeVisible).Address
    End With
    
    Rows("2:22").Select
    Selection.EntireRow.Hidden = True
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
    
    ActiveSheet.Protect
End Sub

[p45cal]

I'm not sure what you want but maybe:

Sub Printsheet()
ActiveSheet.Unprotect
With Application
  .EnableEvents = False
  .ScreenUpdating = False
End With
Rows("2:22").EntireRow.Hidden = True
Dim LR As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
With ActiveSheet
  .PageSetup.BlackAndWhite = True
  .PageSetup.PrintArea = Range("B1:G" & LR).Address
  .PrintOut Copies:=1, Collate:=True
End With
Rows("2:22").EntireRow.Hidden = False
With Application
  .EnableEvents = True
  .ScreenUpdating = True
End With
ActiveSheet.Protect
End Sub

?

[hmltnangel]

I was just coming back on to say I'd solved it .....

This is where I went

Sub Printsheet()
    
    ActiveSheet.Unprotect
    
    Dim rng As Range
        
    Set rng = Range("B2:G606").SpecialCells(xlCellTypeVisible)
    
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    
    Rows("2:22").Select
    Selection.EntireRow.Hidden = False
    
    With ActiveSheet
    .PageSetup.BlackAndWhite = False
    .PageSetup.PrintArea = rng.Address
    .PrintOut Copies:=1, Collate:=True
    End With
    
    Rows("2:22").Select
    Selection.EntireRow.Hidden = True
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
    
    ActiveSheet.Protect
End Sub

All working perfect now.

[p45cal]

then ditch the unhiding of rows then re-hiding them again and use:
Set rng = Range("B23:G606").SpecialCells(xlCellTypeVisible)

Sub Printsheet2()
ActiveSheet.Unprotect
Dim rng As Range
Set rng = Range("B23:G606").SpecialCells(xlCellTypeVisible)
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  With ActiveSheet
    .PageSetup.BlackAndWhite = False
    .PageSetup.PrintArea = rng.Address
    .PrintOut Copies:=1, Collate:=True
  End With
  .EnableEvents = True
  .ScreenUpdating = True
End With
ActiveSheet.Protect
End Sub