PDA

View Full Version : Printing Visible Data Only



hmltnangel
07-22-2014, 03:57 AM
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
07-22-2014, 07:54 AM
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
07-22-2014, 08:12 AM
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
07-22-2014, 09:09 AM
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