printing
 

I need to be able to dynamically print rows where data exists within
Excel, however the following is causing me problems.

Basically "Doc Sheet 1", contains formulas which go from row 7 to 599.
If I try to print this when there is no data in there it then prints
out 600 lines worth of blank pages!

Therefore, following help from yourselves, this is now done using
offset function. The offset function works fine when column C
contains either a "Y" or "N", however I also need this to print out 1
page only (including the titles) if there is no Y's or N's.

Appreciate any advise on this - please let me know if you can help,
but need a copy of the spreadsheet and I will forward this on to you.

Many Thanks as always - Al Mackay ( )

Sub Print_Doc_Sheet_1()
'This sub prints the document control sheet 1

Sheets("Doc Sheet 1").Select
'Select Doc Sheet 1
ActiveWorkbook.Names.Add Name:="Doc_Sheet_1", RefersToR1C1:= _
"=OFFSET('Doc Sheet 1'!R7C2,0,0,COUNTIF('Doc Sheet
1'!R7C2:R600C2,""Y"")+COUNTIF('Doc Sheet 1'!R7C2:R600C2,""N""),19)"
ActiveWorkbook.Names.Add Name:="Doc_Sheet_1", RefersToR1C1:= _
"=OFFSET('Doc Sheet 1'!R7C2,0,0,COUNTIF('Doc Sheet
1'!R7C2:R600C2,""Y"")+COUNTIF('Doc Sheet 1'!R7C2:R600C2,""N""),19)"
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$6"
.PrintTitleColumns = "$B:$T"
End With
ActiveSheet.PageSetup.PrintArea = "Doc_Sheet_1"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("B7").Select

End Sub

printing
 

Sub Print_Doc_Sheet_1()
'This sub prints the document control sheet 1

Sheets("Doc Sheet 1").Select
'Select Doc Sheet 1
ActiveWorkbook.Names.Add Name:="Doc_Sheet_1", RefersToR1C1:= _
"=OFFSET('Doc Sheet 1'!R7C2,0,0,COUNTIF('Doc Sheet
1'!R7C2:R600C2,""Y"")+COUNTIF('Doc Sheet 1'!R7C2:R600C2,""N""),19)"
ActiveWorkbook.Names.Add Name:="Doc_Sheet_1", RefersToR1C1:= _
"=OFFSET('Doc Sheet 1'!R7C2,0,0,COUNTIF('Doc Sheet
1'!R7C2:R600C2,""Y"")+COUNTIF('Doc Sheet 1'!R7C2:R600C2,""N""),19)"
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$6"
.PrintTitleColumns = "$B:$T"
End With
set rng = worksheets("Doc_Sheet_1").Range("B7:B600")
if application.Countif(rng,"Y")+application.Countif(r ng,"N") = 0 then
rng.Parent.Range("A1:J22").Printout ' adjust range to be 1 sheet
else
ActiveSheet.PageSetup.PrintArea = "Doc_Sheet_1"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("B7").Select
End if
End Sub

--
Regards,
Tom Ogilvy

"Al Mackay" wrote in message
om...
I need to be able to dynamically print rows where data exists within
Excel, however the following is causing me problems.

Basically "Doc Sheet 1", contains formulas which go from row 7 to 599.
If I try to print this when there is no data in there it then prints
out 600 lines worth of blank pages!

Therefore, following help from yourselves, this is now done using
offset function. The offset function works fine when column C
contains either a "Y" or "N", however I also need this to print out 1
page only (including the titles) if there is no Y's or N's.

Appreciate any advise on this - please let me know if you can help,
but need a copy of the spreadsheet and I will forward this on to you.

Many Thanks as always - Al Mackay ( )

Sub Print_Doc_Sheet_1()
'This sub prints the document control sheet 1

Sheets("Doc Sheet 1").Select
'Select Doc Sheet 1
ActiveWorkbook.Names.Add Name:="Doc_Sheet_1", RefersToR1C1:= _
"=OFFSET('Doc Sheet 1'!R7C2,0,0,COUNTIF('Doc Sheet
1'!R7C2:R600C2,""Y"")+COUNTIF('Doc Sheet 1'!R7C2:R600C2,""N""),19)"
ActiveWorkbook.Names.Add Name:="Doc_Sheet_1", RefersToR1C1:= _
"=OFFSET('Doc Sheet 1'!R7C2,0,0,COUNTIF('Doc Sheet
1'!R7C2:R600C2,""Y"")+COUNTIF('Doc Sheet 1'!R7C2:R600C2,""N""),19)"
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$6"
.PrintTitleColumns = "$B:$T"
End With
ActiveSheet.PageSetup.PrintArea = "Doc_Sheet_1"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("B7").Select

End Sub