conditional printing

R

raymondsum

Hi,

If Cell A1 = 1 ,Print Area (A2:M6)
If Cell A1 = 2, Print Area (A2:M11)
If Cell A1 = 3, Print Area(A2:M16) ... and so on

How do i write a vb to solve it.

Thanks in advance.

Raymond

Note : I wrongly post this question in tips and tutoria
 
P

Patrick Molloy

Sub SetPrintArea()
Select Case Range("A1")
Case 0
ActiveSheet.PageSetup.PrintArea = ""
Case 1
ActiveSheet.PageSetup.PrintArea = "$A$1:$F$15"
Case 2
ActiveSheet.PageSetup.PrintArea = "$J$1:$K$15"
Case 3
Case Else
End Select

End Sub

Patrick Molloy
Microsoft Excel MVP
 
R

raymondsum

Patrick,

Thank you for your reply. It works.

Would you mind tell me how to write another vb to shorten the length of
programming because number of 'Case' will be more than 100.

Raymond
 
C

Chip Pearson

Raymond,

Try

ActiveSheet.PageSetup.PrintArea = _
"A2:" & Cells(1 + (Range("A1").Value * 5), "M").Address


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Top