Conditional Print Area

[PhilosophersSage]

Is there a way to set a conditional print area? I have a spread sheet that
has several sections and want to print based on a formula. If anyone can help
thanks in advance!

 

[Atishoo]

Use VBA (alt F11)

if --------
then
ActiveSheet.PageSetup.PrintArea = -------
end if

 

[Atishoo]

put it in as a before print command

Private Sub Workbook_BeforePrint(Cancel As Boolean)
if activesheet.range ("A1").value = 2
then
ActiveSheet.PageSetup.PrintArea = range ("a1:d10")
end if

End Sub

 

[Mike H]

Hi,

You should be able to do something like this. right click yoir sheet tab,
view code and paste the code below in. Edit for your ranges and cell to
monitor.


Private Sub Worksheet_Calculate()
On Error Resume Next
Select Case Range("J1").Value
Case Is = 1
ActiveSheet.PageSetup.PrintArea = "$B$1:$E$20"
Case Is = 2
ActiveSheet.PageSetup.PrintArea = "$F$1:$I$20"
Case Else
End Select
End Sub

Mike

 

[PhilosophersSage]

Sorry forgot to mention I cannot use macros due to company security settings.

 

[PhilosophersSage]

Sorry forgot to mention I cannot use macros due to company security settings.

 

[Learing VBA]

Using this same macro, how would you setup the other attributes for print
settings?
Margins
Orientation
Size
Scaling
Quality
Header
Footer
Rows to repeat
Columns to repeat
Gridlines

Thank You
Learning VBA

 

[Dave Peterson]

You may be able to use a defined name if you can come up with a formula that
returns the addresses that you want to use.

My test sheet is named Sheet1.

I want to print columns A:B if A1=1. If A1<>1, then print columns C.

This is what I did:

Insert|Name|define (xl2003 menus)
Names in workbook: Sheet1!Print_Area
Refers to: =IF(Sheet1!$A$1=1,Sheet1!$A:$B,Sheet1!$C:$D)

(You may have to surround your sheet name with apostrophes:
'Sheet 99'!Print_Area
and
=IF('Sheet 99'!$A$1=1,'Sheet 99'!$A:$B,'Sheet 99'!$C:$D)

If you go into file|page setup, you may find that the print range is changed to
a specific range. And you'll have to reapply the Print_Area name.

 

[PhilosophersSage]

That sounds like is should work, but I have played around with what I need
and it does not seem to work. For my first project I need to print only rows
that have data in Column B:F so I tried a few verations of:
=COUNTA('Continuity Sheet'!$B1:'Continuity Sheet'!$F1)>0
However it wants to print all pages, and I understand why as that statment
is True. How would I formulate the function to check for data and extend
print area if there is data. The main reason for this is this sheet has a
formula in A and C are merged except row 1:6

 

[Dave Peterson]

First, =counta() counts the number of cells with something in them--including
formulas that evaluate to "".

I'm not sure what you were doing with the >0 stuff.

=counta('continuity sheet'!$b1:$f1)
would be the way I'd write that expression.

But I don't think that's what you want.

If you're looking to print columns B:F based on the data in column B, then you
could use a name that refers to:

=OFFSET('Continuity Sheet'!$B$1,0,0,COUNTA('Continuity Sheet'!$B:$B),5)

Still using the name: 'Continuity Sheet'!Print_Area

(You can't have any empty cells in column B.)

Debra Dalgleish explains dynamic range names here:
http://contextures.com/xlNames01.html#Dynamic

 

[PhilosophersSage]

Dave, Thank you very much!

Just needed a slight modification to your formula suggestion and used
=OFFSET('Continuity Sheet'!$A$1,0,0,(COUNTA('Continuity Sheet'!$B:$B))+3,5)
works great!

 

[Dave Peterson]

If you're adding 3 to the number of rows, that usually means you have empty
cells in that column.

I like to fill those empty cells with a formula that still keeps the cell
looking empty:
=""

Then =counta() will include it and my formula won't need to change when I put
some (visible) text into that cell.

Dave, Thank you very much!

Just needed a slight modification to your formula suggestion and used
=OFFSET('Continuity Sheet'!$A$1,0,0,(COUNTA('Continuity Sheet'!$B:$B))+3,5)
works great!