Printing Visible Data Only

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 :(

Code:

---

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

---

I'm not sure what you want but maybe:

Code:

---

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

---

?

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

This is where I went

Code:

---

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.

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

Code:

---

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

---